Skip to content

Instantly share code, notes, and snippets.

@maleghast

maleghast/table_copy.md

Last active May 22, 2018 10:17
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save maleghast/eeff990fb9619aed96f81035443a2dc4 to your computer and use it in GitHub Desktop.
Save maleghast/eeff990fb9619aed96f81035443a2dc4 to your computer and use it in GitHub Desktop.
Download ZIP
Aide Memoire for how to move / copy a table from one DB to another under PostgreSQL...
Raw
table_copy.md

pg_dump -> psql

e.g.

  1. pg_dump -U [username] -h [hostname] -p [port number] -d [source database] -t [source_table] > [output_filename].sql

  2. cat [output_filename].sql | psql -U [username] -h [hostname] -p [port number] -d [target database]

If the [target database] already has the table that you want to copy, then run the pg_dump with a "-a" flag to dump 'data only'

This is split into two operations as each will require a password to be supplied interactively. Clearly if you are happy to issue the commands relying on a ~/.pgpass file (i.e. it's being done over SSH) you can pipe the former into the latter without having to use [output_filename].sql as an interim step, thus:

pg_dump -U [username] -h [hostname] -p [port number] -d [source database] -t [source_table] | psql -U [username] -h [hostname] -p [port number] -d [target database]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment