Postgresql Backup And Restore manual
This is not to be the best procedure for backup and restore, but my way how I do this.
For backup there is
pg_dump utility. There is at least one command line parameters that meed to be mentioned.
-F format, --format=format
- p (plain): Output a plain-text SQL script file (the default)
- c (custom): Output a custom-format archive
- d (directory): Output a directory-format archive
- t (tar): Output a tar-format archive
My preffered since verzio 7.2 is custom. To be honest I’m not sure about the compatibility between the postgresql version, but within the same version it works great.
So when dumping the database add
-Fc to your command line. Why? Read restore.
pg_dump -Fc <other parameters> > database_backup.sql.Fc
When trying to restore custom format of the postgresql dump you need the pg_restore utility. As one of the command line parameters there need to be
So what is so amazing on the custom format?
Image, that you do not want to restore full database but
- only the subset of the tables.
- full schema but data for only some tables
I use these 2 scenarios when restoring production several gigabytes big database onto test or development servers.
pg_restore has 2 addition parameters:
-l: list the contents of the archive.
-L list-file: Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file
Editing the list-file you can remove or comment any line you do not need to be restored. So my restore procedure looks like
pg_restore -l database_backup.Fc > database_backup.Fc.list vim database_backup.Fc.list pg_restore <other parameters> -L database_backup.Fc.list -Fc database_backup.Fc