# Comparing Settings From Different PostgreSQL Databases

I'm in the process of migrating from an older version of PostgreSQL to a newer version. I'd like to see what the differences are between the configuration (\${PG_DATA}/postgresql.conf) of both servers. I couldn't find an easy, ready-made solution, so I hacked up one using plain old SQL, which turns out to be particularly well suited to comparing sets of data :)

First, get the settings from the old server. We'll use psql to execute the SHOW ALL query and pipe the result (stripped of all extraneous formatting) to the file old_settings.txt. (I'm using the long versions of the command flags, as well as adding in lots of newlines, to aid in readability and comprehensibility.)

psql --username postgres \
--dbname postgres \
--port OLD_SERVER_PORT \
--output old_settings.txt \
--no-align \
--quiet \
--tuples-only \
--command 'show all'


Now, we'll need the settings from the new server. We use the same trick, but pipe the output to the new_settings.txt file, instead.

psql --username postgres \
--dbname postgres \
--port NEW_SERVER_PORT \
--output new_settings.txt \
--no-align \
--quiet \
--tuples-only \
--command 'show all'


So now we have the data in a format that is easily loaded into a PostgreSQL database! On some other database, we create some simple tables to hold the information; their format is that of the output of the SHOW ALL command.

CREATE TABLE old_server(parameter TEXT, value TEXT, description TEXT);
CREATE TABLE new_server(parameter TEXT, value TEXT, description TEXT);


Now copy the information into the tables using the \copy command:

\copy old_server from ./old_settings.txt delimiter as '|'
\copy new_server from ./new_settings.txt delimiter as '|'


We'll create a view to massage this data into a nice report in order to more easily see what's different:

CREATE VIEW configurations AS
SELECT
older.value as original_value,
(older.parameter IS NULL) AS "new?"
LEFT JOIN old_server AS older  -- there might be some parameters that are no longer there
;


Finally, we can execute some simple queries on this view to show us what's going on:

-- What parameters are different?
SELECT parameter, original_value, current_value FROM configurations WHERE "different?";

-- What are the values of the parameters that are not present in the original configuration?
SELECT parameter, current_value FROM configurations WHERE "new?";

-- What are the values of the parameters that have been removed since the original database version?
SELECT parameter, original_value FROM configurations WHERE "removed?";


Problem solved!