pg_dump and pg_restore can backup and restore single Postgres schemas

Today I learned that pg_dump can make a copy of a Postgres schema instead of the whole database. Likewise, if needed, pg_restore can restore the schema in either the original database or a different one. Backup of a Postgres schema: pg_dump -h host -d source_database -U user -n schema_name -F c -f schema_dump_file.dump Restore of a Postgres schema: pg_restore -h host -d dest_database -U user -n schema_name schema_dump_file.dump All primary and shared knowledge, I am sure....

January 30, 2024

pg_rman: a backup and restore management tool for PostgreSQL

The goal of the pg_rman project is to provide a method for online backup and PITR that is as easy as pg_dump. Also, it maintains a backup catalog per database cluster. Users can maintain old backups including archive logs with one command. We’ve always been doing our Postgres backups the rudimentary way via pg_dumpall, which works and is purely logical (one can restore across different Postgres versions), but pg_rman maintains a catalog and has point-in-time recovery....

January 9, 2024

Awesome psql tips

Today I learned about by Lætitia Avrot, an excellent repository of psql (the CLI tool, not the database itself) tips. I like how one randomized tip is playfully served on the home page while the complete list is always at hand.

February 23, 2023

Automatic deletion of older records in Postgres

We have a Postgres cluster with a database for each user. Each database has a table that records events, and we want this table to only record the last 15 days. If we were on MongoDB, we could use a capped collection, but we are in Postgres, which does not have equivalent functionality. In Postgres, you have to make do with something homemade. My first idea was to install a cron job in the system....

January 16, 2022

How to restore a single Postgres database from a pg_dumpall dump

Today I learned how to restore a single Postgres database from a global dump generated with pg_dumpall. Now, pg_dumpall is handy when you want to back up an entire Postgres cluster. It will dump all databases and global objects in a single text file. In contrast, pg_dump, the go-to tool for Postgres backups, offers more control but only works with a single database and doesn’t dump global objects, such as the roles/users linked to the database....

August 25, 2021

Cleaning Up Your Postgres Database

I am an application/backend developer who has to quibble with databases more often than desired. I can get my way around Postgres pretty well, but I can always use a hint or two, especially when it comes to fine-tuning and performance. I stumbled upon Cleaning Up Your Postgres Databases. It offers useful advice on spotting performance bottlenecks in your Postgres database. Take the cache and index hit queries, for example....

March 9, 2021