PostgreSQL Backup, Restore and Recovery

/ home / computer / postgresql / postgresql-for-mysql-admins / .

Logical Backup (SQL dump)

• mysqldump/mysql vs. pg_dump/pg_restore

Difference to MySQL: pg_dump backup CANNOT be used as base for a Point-in-Time-Recovery! Use physical backup instead.

https://www.postgresql.org/docs/current/backup-dump.html

PostgreSQL Physical Backup

• xtrabackup/ mysqlbackup vs. pg_basebackup

https://www.postgresql.org/docs/current/app-pgbasebackup.html

Backup methods

• File system copy
• Snapshots
• PostgreSQL physical backup

https://www.postgresql.org/docs/current/backup-file.html

Point-in-Time-Recovery

• MySQL binary log vs. PostgreSQL write-ahead-log (WAL)
• Managing the WAL
• Doing a PiTR mysqlbinlog vs. …

WAL archiving.

pg_basebackup + WAL replay

https://www.postgresql.org/docs/current/continuous-archiving.html

Testing your restore!