I have been working with SQL Server since 2005, and while I love SQL Server, I also love to learn. One of the platforms I’ve been hearing more about lately is Postgres. It is being referenced more in blog posts, conference sessions, and I even noticed some SQL Server engineers at Microsoft moving over to the Postgres team.
Those couple of events piqued my interest so a few months ago I dipped my toes into the Postgres pool. I started this adventure looking for ways to apply my 20+ years in SQL Server to Postgres so that I can start to help people who are also new to the platform. One of the very first things I learned about in SQL Server was backups, so I thought it would be fitting to start there with Postgres.
Most of the readers of this post likely know how SQL Server backups work. You can create a backup of an individual database with a full backup, a differential backup, and a log backup. You can then use those backup files to restore the database onto the same server or a different server.
With Postgres, there are two categories of backups: a logical backup, and a physical backup. The logical backup is essentially an export of one or more objects in the database to a file. The contents of that file could have anything from the scripts to create the objects to the insert statements to reload the data into a table. But that is all you can really do with a logical backup – create a copy of something in a database and restore it somewhere else. Useful for certain tasks, but this cannot be used for other administrative tasks like point in time recovery or building physical standby replicas.
A physical backup is where you would start if you needed to perform a point in time restore of a database. Comparing this to SQL Server this is where you’d restore a full backup, then maybe a diff backup, and then roll log backups until you get to your desired end time.
One of the big differences between SQL backups and physical Postgres backups is with SQL Server you backup one database; with a physical Postgres backup, you capture the entire Postgres cluster. One of the reasons for this is that all the databases on the Postgres cluster share the same Write-Ahead Log (WAL) file. Because all databases share the WAL file, everything on the cluster is needed for Postgres to guarantee a database is transactionally consistent. If there were objects missing from the cluster, it could cause failures while transactions are being replayed from the WAL file backups.
This was a big difference for me when comparing SQL Server and Postgres. With SQL Server I only need to worry about a couple database files and if there is space on the server, but with Postgres there is so much more to take into consideration when preparing to do a point in time restore of a database.
Looking forward to learning more about the differences between the two platforms and if there are any similarities.