postgis moving tiger data

Question

Is there a way to move tiger, tiger_data to another database on another host?

Without having to use the loader scripts?

If I use pg_dump I get no data.

Answer 1

Yes I do it all the time.

What version of PostGIS are you running?

SELECT postgis_full_version();

And postgis_tiger_geocoder:

SELECT extversion FROM pg_extension where extname = 'postgis_tiger_geocoder';

There was a bug a while ago that I fixed that the tiger_data schema was part of postgis_tiger_geocoder extension. That prevented pg_dump from dumping the data.

To fix that if you are bitten by it, do the following:

ALTER EXTENSION postgis_tiger_geocoder DROP SCHEMA tiger_data;

Then you should be able to backup your data. If you had done

ALTER EXTENSION postgis_tiger_geocoder UPDATE;

and have a fairly recent postgis_tiger_geocoder installed, it should have dropped the schema from the extension.

For the tiger schema, not much gets backed up aside from I thing geocoder_settings and loader_platform.

But you wouldn't really need that unless you made changes to functions packaged with the geocoder, which you shouldn't. Other functions you make not part of the extension even if in the tiger schema should get backed up.

On other side do

CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

Then load your data as usual.

SELECT install_missing_indexes();

I think some indexes might fail to load during the restore because fuzzystrmatch functions aren't schema qualified in the tiger schema.

Answer 2

Assuming your two hosts are on the same machine or can talk to each other via the Internet, one way to copy any data between hosts without dumping and loading is to use Postgresql's useful foreign data wrapper (FDW). On old versions of Postgresql you may have to use dblink which does pretty much the same thing from an SQL query perspective. For a quick how-to on using FDW see here.

Once you have set up your FDW table on the remote host (which is effectively a window into your original table on the original host) you can use it as though it were local to the new host and/or create a new table locally. The speed hit of using FDW will depend a lot on the data, use case, queries and network. I have data scattered across several different machines and often use it this way (without bothering to actually create a new copy of the table locally on the second server - you could, but don't have to).