Work: How to Partially Migrate a PostgreSQL database
Assuming you have database on hostA and want to copy it to this host:
- Create the database on hostB
- Migrate the entire schema (without data) across:
- Migrate data in certain tables (i.e. NOT climate* and forecast* tables) only:
sudo su - postgres "createuser --pwprompt --superuser --createdb newdba -h hostB"
createdb -U newdba newdb
pg_dump -C -s -U olddba -h hostA olddb | psql -U newdba newdb
pg_dump -c -Tforecast* -Tclimate* -U olddba -h hostA olddb | psql -U newdba newdb
Note: you may need to type “reset” after the pg_dump is complete to reset your terminal to echo mode; having two password prompts confuses it a bit.
