Compare PostgreSQL Databases in Seconds

Note: this post is sponsored by Redgate, the creators of pgCompare.

If you've ever had to compare two Postgres database schemas, you know how painful that can be.

You know it’s important because you don’t want to deploy the wrong thing or introduce a bug.

Maybe you export both schemas, open them in a text editor, run a diff, scroll through a massive wall of SQL, and hope you spot all the differences.

In this post, I’ll show you a tool that makes that entire process much easier.

What is pgCompare?

This tool is pgCompare, a tool by Redgate that allows you to compare two PostgreSQL databases and show you exactly what’s different between the two.

It even generates a safe deployment script that lets you apply those changes in the correct order, along with any warnings if anything destructive might happen.

There’s a free community edition you can download from the Redgate website here. I'll explain more about this later in the post.

Connect and Compare

This is the pgCompare tool. I’ve already installed it, and it was pretty easy to install.

Let’s jump in and compare two PostgreSQL databases: a dev database and a test database.

We can see two sections at the top of the window here: a Source database and a Target database. We can use these sections to connect to two different databases and compare them.

They can be two local databases, or two cloud-hosted databases, or a mix of both. As long as you can connect to them, you should be able to compare them.

I’ve already connected to the databases, which is why there is data showing here, but when you open this for the first time, you’ll see these are empty.

Source Database

We’ll start by clicking on Source. Generally, the source database is where you have made changes, and the target is where you want to deploy changes. For example, if I make changes in the dev environment and want to compare the database to the next environment up, which could be the test environment, then the source would be Dev and the target would be Test.

For this example, I’ll connect to a local Postgres database, which can be our dev database.

Click the Source button, and a connection window will appear.

Enter the details for your database that you want to connect to. I’ll enter the hostname and port. Then, for the name of the database, I’ll enter “postgres_dev”, which is the name of my dev database for this demo. I’ve already set up this database.

I’ll enter the username and password for this, leave SSL mode as disabled, and then click Save Connection.

The Source database is now set up.

Target Database

Now, let’s select our target database.

In this example, we’re using a different database on my local computer, called postgres_test. The connection details are very similar to the dev database.

If you’re using this in your workplace, you’ll likely have different connection details, probably a different hostname and maybe a different database name.

After I enter the details, I click Save Connection.

Now the database is connected.

Differences

Once both databases are connected, pgCompare will look at them both and display a list of differences.

You’ll get a clear breakdown of these differences.

You can see any objects that are only in one database and not the other, such as new tables you’ve created in Dev but haven’t yet deployed to Test.

For example, this entry here for the table called “department” shows a table that I have created in Dev, but not in Test. We can see the change type is “Only In Source”.

You can also see if any tables have changes, such as missing columns or new columns. This example here, of the employee table, shows a table that exists in both databases but is slightly different between the two.

How do we know what the differences are?

You’ll be able to see line-by-line differences in functions or views. We can see a function here that has changes, as well as a view.

There’s no need to run any more pg_dump commands or file diffs. You just open this tool and compare the databases.

Explore the Differences

Let’s drill into some of the differences.

First, we’ll click on the new department table: the table that has been created in dev but not in test.

We can see more details about the differences in the panel that appears at the bottom.

In the Source database, we can see the SQL statement that shows the new table has been created. It has the columns for the new table as well as the primary key.

In the Target database, on the right, we see nothing because the table does not exist.

What about a table that exists but has been changed?

We can click on this entry for the employee table here. We can now see the details of the change, which shows that the start_date column has been added in the dev database or the Source, and does not exist in the test database, which is the target on the right.

pgCompare shows you which column is different and highlights the change.

Let’s see the view that has changed. We can click on this view, called employees_100k_plus, and we can see in the panel that appears how it has changed. There’s a minor change: the greater than has changed to a greater than or equal to, but pgCompare has still picked it up.

What about a function? This function is showing as having differences in the two databases. We can click on this entry here, called findEmployeeCount, which seems to have a difference.

We can see the individual lines of the function that are different in both databases. This is pretty handy. It not only compares the simple things like tables and columns, but also the differences in functions.

Generate Deployment Script

Once you’ve reviewed these changes, the next thing you’ll probably want to do is to generate a script to apply the changes to the target database.

You can do this very easily in pgCompare.

To do this, you’ll first need to identify which objects you want to include in the script. If you want everything, just click the checkbox on the top of this column here.

If you only want some objects, you can adjust this based on what you need, by clicking these checkboxes next to each entry. For this example, I’ll assume you want every object.

Now, we click the Generate Script button.

A screen appears with the SQL script to apply the changes to the target database. This window has syntax highlighting and line numbers, making it easy to read. You can scroll down and see how the SQL is written to apply the changes.

From here, you can either copy the changes to the clipboard if you want to paste them somewhere else.

Or, you can save the script to an SQL file on your computer. This is handy if you want to save the script to your Github repo and use it in your CI/CD pipeline.

Also, this generated script has all of the objects in the right order, meaning that any dependencies are handled, and you won’t get errors about referencing objects before they are created.

Make Schema Changes and Re-Compare

Let’s say we’re working on a change to our database while we have this tool open. Let’s see what happens when we make some changes to the dev database.

We’ll create a new table here, called audit_log.

1CREATE TABLE audit_log (
2  id SERIAL PRIMARY KEY,
3  action TEXT NOT NULL,
4  created_at TIMESTAMP DEFAULT NOW()
5);

We run this and it is successful.

Let’s go back to pgCompare.

How can we see these changes in our tool?

We can simply click the refresh button. The new table, called audit_log, now shows in the list.

When we click the Select All checkbox, this new table is included in the list for generating the script.

View Changes

There are a couple of other features here that make the list easier to understand.

You can group your changes by no group, by change type, or by object type.

This can help you better understand your list of changes.

You can also ignore objects that are equal in both schemas, and ignore line endings in files.

There’s a dark mode available as well, with a click of a button here.

Real-World Use Cases

There are a few ways you can use pgCompare.

Prepare for a deployment: First, you can use it to prepare for a deployment and review schema or database changes, like we have done in this example.

Investigate a bug: Another use is if you’re investigating a bug in production and want to check if the databases are the same. You can compare production to a lower environment such as UAT or Staging to see what may be different.

Generate a change script: You can also use it to generate a clean change script for your version control.

Also, because pgCompare is cross-platform, you can use it on Windows, Mac, or Linux.

Final Recap

This pgCompare tool makes it very easy to understand what has changed between your databases and deploy with confidence.

There’s a free and a paid tier for pgCompare. The free tier is free for non-commercial use or small businesses.

The free tier has, in my opinion, a pretty generous limitation for usage, which is based on revenue. The paid tier should also be valuable for you as well - the time and hassle it can save you with your deployments is well worth it.

If you want to check it out for yourself, you can download pgCompare from the Redgate website here.

Thanks for reading.