Setting up the Stack Overflow database in Postgres

I’ve worked with Microsoft SQL Server for many years as well as dipping my toes into the MySQL world as and when required, however, I have not done as much with PostgreSQL. It’s hard to ignore the momentum that Postgres has built in recent years; it is moving up the Stack Overflow Developer Survey and the Redgate DB-Engines Ranking, one of the most recognisable names in the Microsoft SQL Server world, Brent Ozar, has begun to teach it and monitoring tools that were previously SQL Server only have begun to monitor it.

This has piqued my interest and I thought it time to start looking in greater detail at Postgres.

To support this, I needed to set up an environment to do so and to get some kind of real-life sized test database and that is what is covered in this article.

The Test Database

The test Stack Overflow database is made available by Brent Ozar at SmartPostgres.com. For many years Brent has made The Stack Overflow Data Dump available as a SQL Server Database to support the community’s learning. I have found the Stack Overflow Database a great resource to recreate real-world scenarios given its scale and the fact it has the intricacies that come with real-world data.

Brent has now made this available to download for Postgres and this is what I set up in my Postgres test lab.

The database is provided under cc-by-sa 4.0 licence from the Stack Exchange Data Dump.

Installing Postgres

I am running Debian 12 on a VM and will install Postgres from the stable repository, which at the time of writing contains Postgres version 15. The VM has the following specs, which I can tweak as desired as time rolls on (I started small):

4-core CPU

16GB RAM

170GB Hard Disk

VM creation and installing Postgres are beyond the scope of the blog, but we will work from the point where we have our VM created and Postgres 15 installed.

Downloading the Backup File

I downloaded the large 117GB file from SmartPostgres and added it to an SMB share on my file server, making the share available to my Postgres VM by mounting the share:

sudo mount -t cifs -o credentials=/home/dualcoredba/.credentials //TheFileServer/TheShare /home/dualcoredba/TheShare

The share is now mounted at /home/dualcoredba/TheShare on the Postgres VM.

Restoring the Backup

Now I could restore the file using psql:

I SSH’ed into the Postgres VM and logged into the postgres database using psql (the argument is the database name). I didn’t specify a user in the command and so logged in as the OS user the session is running under:

psql postgres

I then needed to create a blank database to restore the file to:

CREATE DATABASE stackoverflow;

Then I logged out of psql, changed directory to the mount point of my share and tried to restore the file:

exit

cd ~/TheShare
psql -d stackoverflow < dump-stackoverflow-202408100709.sql

We see an error that the input file is a custom dump format, meaning it is in a compressed binary format and also supports individual object restoration (the holy grail for us SQL Server users!)

pg_restore is required to restore custom dump files (it does actually say to use it on the SmartPostgres page!) so to restore requires:

pg_restore -d stackoverflow dump-stackoverflow-202408100709.sql

And with that, we’re off!

There are a few errors because I didn’t have the role on my cluster that owns the tables in the backup, but the database will restore without the role being present.

We can monitor progress of the restore in pgAdmin in the state tab, which shows active sessions.

Once in pgAdmin, I opened the state tab in the dashboard. I couldn’t see a nice percent_complete value like SQL Server has, but I can follow what is happening via the statements. There is a lot of difference between backing up SQL Server and Postgres and it is well documented here.

After this, I just left it running to do its thing and at the end, I could see a familiar-looking Stack Overflow database:

Changing Some Default Parameters and Installing Extensions

There is a PostgreSQL extension called pg_stat_statements that I wanted to enable, which gives cumulative query runtime stats. To install it, I need to run some commands in psql again, so I logged in as before:

psql postgres

Once in the psql terminal, run:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

pg_stat_statements also requires a system configuration change, which can either be done by editing postgresql.conf manually or using the ALTER SYSTEM SET command from psql. The setting we need to change is shared_preload_libraries. As this setting is a postmaster context setting, it requires a service restart to take effect. We can use the following to change a setting for the entire system:

ALTER SYSTEM SET setting = 'value';

Having looked around a number of articles, I found a few default settings that are generally considered inadequate and so I changed them too. Some of these are in the user context rather than postmaster, which means they can be changed per connection or per transaction. Changing settings system-wide uses the same approach as for postmaster-context settings: ALTER SYSTEM SET, followed by a configuration reload (or service restart, for postmaster context settings).

The changes I made were:

/* enable pg_stat_statements */
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

/* increase from defaults */
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '128MB';

/* 25% of server memory */
ALTER SYSTEM SET shared_buffers = '4GB';

As before, the terminal confirmed success by echoing the command name back to us:

Finally, we need a service restart for the settings in the postmaster context to take effect. Back at the bash prompt, the following command will restart the service: