postgres pg_restore - ghdrako/doc_snipets GitHub Wiki

pg_dump creates a portable file format that can be used to transfer data between different PostgreSQL installations or even between different database management systems.

pg_restore is a PostgreSQL utility that restores a backup file created by pg_dump or a similar tool. It reads the SQL statements from the backup file and applies them to a new or existing database, creating a copy of the original database. pg_restore can be used to restore the entire database, specific tables or schemas, or even individual records.

Restore to existing database

In example with change collation

pg_restore -d target_database /path/to/the/backup.dbbackup
  • -d --dbname: specifies the target database to restore to. By default, pg_restore creates a new database with the same name as the original database.
  • -F: specifies the backup format. The default format is plain text (-Fp), but you can also use binary (-Fc) or directory (-Fd) formats.
  • -c or --clean: Drops database objects before recreating them. This ensures a clean restoration.
  • -C or --create: Creates the target database before restoring the data. If the database already exists, pg_restore will exit with an error unless the --if-exists option is also specified.
  • -j or --jobs: Specifies the number of concurrent jobs for parallel restoration.
  • --if-exists: This option is used with the -C option. It tells pg_restore to ignore errors if the database being created already exists. If the database exists, pg_restore will proceed with the restore without attempting to create it again.
  • -t or --table to restore only specific tables,
  • -n or --schema to restore specific schemas
  • -l or --list: to view the table of contents of the backup file,

Reorder restore or change to specified objects

Generate list object in backup

pg_restore -l ecommerce_backup.dmp >  db.list

Edit list in example remove or change order tables Semicolons start a comment, and the numbers at the start of lines refer to the internal archive ID assigned to each item.

Lines in the file can be commented out, deleted, and reordered. For example:

10; 145433 TABLE map_resolutions postgres
;2; 145344 TABLE species postgres
;4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
;8; 145416 TABLE ss_old postgres

could be used as input to pg_restore and would only restore items 10 and 6, in that order:

$ pg_restore -L db.list db.dump

Restoring single table

-t <nazwa_tabeli> -n 

pg_restore -d "sslmode=verify-ca sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr=10.90.251.13 port=5432 user=ebkadm password=ebkadm dbname=ebkdb3" -t profile_funkcje_slw -n ebk -vvv /mnt/disks/sdb/ebkdb20240904a.dmp

Example

pg_restore -U postgres -W -C -c --if-exists -d postgres /tmp/ecommerce_backup.tar

Restore to recreated identical database

If you are certain that there is no data of any value in the target database (which will be deleted by the following command) try:

sudo -u postgres pg_restore --create --clean -d postgres /path/to/dbbackup.sql
  • –c --clean : remove database objects before recreating them
  • -C --create : construct a database before restoring it
psql "sslmode=verify-ca sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr=<ip> port=5432 user=postgres dbname=postgres"
pg_dump "sslmode=verify-ca sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr=10.223.208.206 port=5432 user=ebkadm dbname=ebkdb" --format=custom \
--no-owner \
--no-acl \
 > ebkdb.dmp


pg_restore -d "sslmode=verify-ca sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr=<ip> port=5432 user=ebkadm dbname=ebkdb"  ebkdb.dmp

Restoring from zip

zcat dumpfile.gz | pg_restore -d db_name

pg_dump -U username -d database_name | gzip > backup_file.sql.gz
gunzip -c backup_file.sql.gz | pg_restore -U username -d database_name

Performance

PG_RESTORE | always use tuning for postgres.conf and format-directory and -j options

work_mem = 32MB
shared_buffers = 4GB
maintenance_work_mem = 2GB --4GB
full_page_writes = off
autovacuum = off
wal_buffers = -1
max_wal_size = 4GB

time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/

restore from a custom-format file with multiple jobs running in parallel:

pg_restore -U username -d database_name -j 4 backup_file.custom