postgres

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....

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....

Today I learned about psql-tips.org 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.

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....

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....

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....