Backing up and Restoring Postgres using pg_basebackup

There are several great tools available that handle backing up and managing the backups of your PostgresQL database. It is really important to understand the underlying process that these tools use though as well as the standard postgres commands are that you would need to run in case you ever need to do it manually.

There are 2 types of backups that you can take in Posgres, logical and physical. Logical backups are in the form if the SQL statements necessary to recreate the database (not necessarily in a human readable form). The 2 tools to take logical backups are pg_dump and pg_dumpall.

Continue reading “Backing up and Restoring Postgres using pg_basebackup”

What is the pg_clog and the clog

There are several directories named log in a Postgres installation.

You have pg_xlog, pg_log and pg_clog.

These are all important but I’ll talk about the others another time.

Pg_clog is the commit log. It is generally a small folder that you should never have a reason to look at. (note that from version 10 of postgres the pg_clog directory is being renamed to pg_xact I will continue to refer to it as pg_clog in this document but the functioning of both is the same)

Importantly, you can never delete anything from that directory. If you do your database will become unusable and you will need to recreate it from a backup.

Continue reading “What is the pg_clog and the clog”

Setting up a Postgres test cluster in vagrant

Most of the time its fine to test things out on your local machine and local installation of Postgres but often, you want more flexibility, the ability to quickly reset to a known starting point and the ability to try out different and more complex server architectures.

That is where Vagrant comes in. I use it to quickly set up Postgres clusters at different versions and different configurations.

You can find all of these in my repo at https://github.com/philmcc/postgres_clusters

For example here is a set up to allow you to set up a 3 node cluster with 1 leader and 2 followers all on postgres 9.4 and using replication slots:

Continue reading “Setting up a Postgres test cluster in vagrant”

Get all of the tables linked by a Foreign Key

Here is a handy little PostgreSQL query that will list all of the tables linked by a foreign key to the table YOUR_TABLE.
The output will include the keys on each side.
This can be very useful if you want to build up a map of your database through the relationships between the tables.

select confrelid::regclass, af.attname as fcol, conrelid::regclass, a.attname as col
from pg_attribute af,
pg_attribute a,
(select conrelid,confrelid,conkey[i] as conkey,
confkey[i] as confkey from (select conrelid,confrelid,conkey,confkey, generate_series(1,array_upper(conkey,1)) as i from pg_constraint where contype = ‘f’) ss) ss2
where af.attnum = confkey and af.attrelid = confrelid and a.attnum = conkey and a.attrelid = conrelid AND (conrelid::regclass = ‘YOUR_TABLE’::regclass);