Journey To PostgreSQL #4

Photo by Mr Cup / Fabien Barral on Unsplash

The most important about database is Backup. Acctually if you create some database cluster basically is about backup. Enable the archive log on oracle or binary log on MySQL is about backup. Setup RAID on storage system is also about backup, so let’s discuss about backup.

Logical Backup

this backup is export you database from binary to text like for example to .sql format or .csv or etc.

As we know MySQL or MariaDB is have tools mysqldump for logical backup, and on Oracle Database has expdp for dump proses.

Postgresql is have pg_dump as default for logical backup, you can check the official document on this link

pg_dump

But my habit I using this option for running backup specific database using pg_dump

# IF Schema Only
pg_dump -U <username> -h <host> --no-privileges --no-owner --schema-only -d <databasename>  > <pathbackup>/<filebackup>

# IF Schema + Data
pg_dump -U <username> -h <host> --no-privileges --no-owner -b -d <databasename>  > <pathbackup>/<filebackup>

If you need some compress option you can add them. And don’t forget logical backup is very helpful for small database. These rules apply to all databases.

Photo by Denny Müller on Unsplash

Physical Backup

This backup is instance level, you don’t need declare every schema database for backup you only need call the instance, and will be backup all database on your instance when online, but this backup need more configuration on postgresql.

MySQL and MariaDB has xtrabackup or mariabackup for physical backup process, and oracle has RMAN by default.

Physical backup is very helpful if you maintain big databases.

Postgresql has pg_basebackup by default for physical backup.

pg_basebackup

But remember like Oracle and MySQL or MariaDB you need enable logfile like Archivelog on Oracle or binarylog on MySQL and MariaDB. Postgresql said them as wallfiles.

If you need some PITR (point in time recovery) you need enable wallfiles on postgresql.conf.

# postgresql.conf
...
wal_level = replica
...
archive_mode = on

Remember if you enable them make sure your instance have more free space on storage, because after enable them every single transaction will be written on wallfiles or archivelog.

Conclusion

So on this part we learn how improtant about backup.

• Small databases is ok use logical backup
• Big databases you must use physical backup

And as DBA you must be check all database instance is have backup on database level. I will make some demo for backup and restore physical level on postgresql, so keep in touch on my social media twitter or linkedin

Don’t forget read more my article here