Thursday, November 1, 2012

PostgreSQL - Copy Table to Another Server

My previous workflow for copying tables from one server to another was (1) pg_dump on server "A" then (2) SCP to server "B" then (3) restore via psql. Not a terrible workflow, but my new workflow is much more eloquent. 

First, I login to server B (my new home for the data), then pg_dump with connection params set to serverA (current location of data). Then pipe directly to psql with connection params set to serverB a.k.a. localhost. 

pg_dump -t schema.table -h serverA -U username databaseA | psql -h localhost -U username databaseB;

An important side note:

If your postgres databases are of different vintages, you should always pg_dump using the latest vintage i.e. If serverB is 9.1 and serverA is 8.4, dump using serverB. pg_* are usually backwards compatible, but not always forwards compatible. 

No comments:

Post a Comment