PostgreSQL: Backup and Restore(large databases)

For very large databases, you might need to combine split with one of the other two approaches.

1.

nohup pg_dump -h 192.168.x.x -d dbname -U username -Fc > filename.dmp 2>&1 &

-F format (--c custom)


A custom-format dump is not a script for psql, but instead must be restored with pg_restore, for example:

$ dropdb mydb
$ pg_restore -C -d postgres db.dump

$ createdb -T template0 newdb
$ pg_restore -d newdb db.dump

2.

nohup pg_restore -C -d newdb filename.dmp 2>&1 &

-C
--create
Create the database before restoring into it.
-d dbname
--dbname=dbname

-O
--no-owner
Do not output commands to set ownership of objects to match the original database. By default, pg_restore issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created schema elements. These statements will fail unless the initial connection to the database is made by a superuser (or the same user that owns all of the objects in the script). With -O, any user name can be used for the initial connection, and this user will own all the created objects.