Geek Answers Handbook

What pgdump format is best for small memory and fast recovery?

This is the first time in PostgreSQL backups (db dumps), and I studied various pgdump formats, other pgdump and pgdumpall options. For a beginner, Postgres, who is looking at getting an hourly dump (overwrites the previous dump) of two databases containing table triggers and two different schemes in each db, what will be the format and backup options to easily achieve the following:

  • Small file size (one file for each bit or the ability to choose which db to restore)
  • Easy to restore as pure db (with and without the same db [s] name)
  • It is easily restored on different servers (the user may be different)
  • Triggers are disabled during recovery and re-enabled after recovery.

Include sample commands for backup and restore.

Any other useful pgdump / pgrestore suggestions are welcome.

+4
source share
2 answers

This command will create a small dmp file that includes only the structure of columns, columns, triggers, views, etc. (this command only takes a few minutes)

pg_dump -U "dbuser" -h "host" -p "port" -F c -b -v -f ob_`date +%Y%m%d`.dmp dbname

**ex:** pg_dump -U thames -h localhost -p 5432 -F c -b -v -f ob_`date +%Y%m%d`.dmp dbname

This command will back up the full database.

pg_dump -h localhost -U "dbuser" "dbname" -Fc > "pathfilename.backup"

**ex:** pg_dump -h localhost -U thames thamesdb - Fc > "thamesdb.backup"

and for recovery you can use:

pg_restore -i -h localhost -U "user" -d "dbname" -v "dbname.backup"

**ex:** pg_restore -i -h localhost -U thames -d thamesdb -v "thamesdb.backup"

to backup selected tables (uses regular expressions) here

pg_dump -t '(A|B|C)'

for more information, you can visit the pgdump man page, there are many options

+3
source

, , pg_dump.

-1

More articles:


All Articles