How to backup a single postgresql table with pg_dump
Here are the commands I found to work best in postgresql version 8.4:
commands to copy tables to /home/raid temp
#change dir to tmp:
#change user to postgres
#enter the following sql command replacing did_serial with actual table name:
pg_dump -a -t did_serial -f did_serial.sql maximize_master
How to move a Postgresql database to a new server
I had to move a very large postgresql database from one server to another recently. The old server was version 8.4 and new was 9.2.1
Here is the pg_dump command I used from Linux command line:
here is the pg_restore command done from regular Linux command line:
pg_restore -C -d postgres db.dump
pg_dump restore with pg_restore
In order to restore a postgresql .dump file made with page_dump that was done with the -Fc parameters, as in the following example:
pg_dump -Fc mydb > db.dump
To restore a dump that used the above command, use the following pg_restore command at command line
pg_restore -C -d postgres db.dump
Where C parameter means to create table and d parameter is the database. postgres can be any database name in the cluster. You have to provide another database name even though it gets the actual database name from the dump file. Don’t worry, it won’t rename the database postgres, it just needs to postgres database to create the new database for some reason.
I also wanted to try the -O option because ownership of the tables was all messed up so I tried:
pg_restore -O -C -d postgres db.dump
The reason for the -O option was that when I transferred the first time, the database has users and roles that didn’t exist on the new server, so the -O option makes it owner neutral, which seems to have solved the issue.
Tips and tricks:
- use the df -h command on a second terminal window to see if the database is loading. On larger databases, like the one I did recently which was a 15 gig backup file, it will hang on the pg_restore command seemingly indefinitely. You can type [df -h] in a second command line interface to monitor progress. Do it once then do it again and compare to see that there is more disc space being used. that is the technique I used to verify that the pg_restore command was working.
- Errors – When I did this I got an error saying:
pg_restore.bin: [archiver (db)] Error while PROCESSING TOC:
pg_restore.bin: [archiver (db)] Error from TOC entry 2672; 2612 4376101 PROCEDURAL LANGUAGE plpgsql postgres
pg_restore.bin: [archiver (db)] could not execute query: ERROR: language “plpgsql” already exists
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
but that is just because the database already had the plpsql language in it which is not a big deal and shouldn’t mess up anything, so just ignore the error and patiently wait for pg_restore to finish.
- Leave the command prompt alone – It seems like I messed up one restore session by messing around with keyboard when the focus was on the terminal window where I did the pg_restore command from, so to avoid the likelihood of this happening, leave the terminal window alone until it returns a new command prompt indicating that it has finished.
- Missing Data – if something went wtong and some tables are missing data. you can do what I did and do a backup for each table that is messed up. Use the following command to pg_dump just the table you need from the original database server:enter the following sql command replacing did_serial with a table name:
pg_dump -a -t table_name -f table_name.sql db_name
replace table_name and db_name with the table and database names respectively. Then download the file to your desktop, open it and take out everything before the the copy command and save it. upload to new server and issue following command:
- psql db_name < table_name.sqlNOTE: you have to be in the directory of table_name.sql or use the path before it.