Logical backup extract metadata(with SQL) from postgresql.
Its advantages includes human readable text and better transplantability.
Dump objects or single
database into file.
pg_dump [connection-option…] [option…] [dbname]
Just in one file, long if database becomes big.
pg_dump -U postgres -h 127.0.0.1 -t test > test.sql
psql -U postgres -h 127.0.0.1 -f test.sql
Directory organized, data and table separated, cleaner and easier to manage.
Might use more storage. Transplantability not as good as plain format.
pg_dump -U postgres -h 127.0.0.1 --format=tar -t test > test.tar
pg_restore -U postgres -h 127.0.0.1 --dbname=postgres test.tar
Dump whole postgresql
cluster into file.
This command actually calling
pg_dump and dump each database in this cluster one by one.
pg_dump -U postgres -h 127.0.0.1> pg.sql
psql -U postgres -h 127.0.0.1 -f pg.sql
Physical backup somehow copies files that related with database directly.
Only physical backup could use
PITR technique. If you need to use
PITR, please enable
archive log. For this part, please refer to my post;
This command will copy files under
$PGDATA as well as tablespace files that outside this folder.
Beware this not includes
archived WAL files. You need to backup them up manually.
pg_basebackup -U postgres -h 127.0.0.1 --format=tar -xz -P -D backup
restore: Just copy and unzip required tar files to specific path.
You can also use system commands like
cp to manually copy and compress files. But if you do so, you need to stop postgresql cluster first to ensure data consistency in database.