NullifyNetwork

While .Net has fully native Postgres support using the Npgsql library, it requires using the approach of telling the connection that you require a read-write intent in code and not the connection string, so you end up with a normal connection string with a list of servers:-

    Host=server1.example.org,server2.example.org;Username=myusername;Password=mypassword;Database=mydatabase

This connection string will use both server1 and server2 to handle all requests if used by default, however if you use the NpgsqlDataSourceBuilder and call CreateConnection, you can specify TargetSessionAttributes.ReadWrite as a parameter to guarantee you get a writeable master server:-

    var dsBuilder = new NpgsqlDataSourceBuilder(Configuration.GetValue<string>("Database:ConnectionString"));
    var ds = dsBuilder.BuildMultiHost().CreateConnection(TargetSessionAttributes.ReadWrite);

This will enable you to have read, and read-write intent on your applications connection to the database.

But what happens if you're not using .Net and want to interact with the writeable server first?

It turns out there's a target_session_attrs parameter:-

    postgresql://server1.example.org,server2.example.org/mydatabase?target_session_attrs=any
    postgresql://server1.example.org,server2.example.org/mydatabase?target_session_attrs=read-write

And this is an option on the ODBC driver too:-

    Driver={PostgreSQL};Server=server1.example.org,server2.example.org;Port=5432;Database=mydatabase;Uid=myusername;Pwd=mypassword;target_session_attrs=read-write;

There are other parameters you can use here too:-

any

read-write

read-only

primary

standby

prefer-standby

During my switch to Postgres, I also deployed an active/passive physical replication cluster, at the time on Postgres 16.  I wanted to upgrade to Postgres 17, so did a backup:-

    pg_dumpall > 20241209-PreUpgradeBackup.bak

Created a new Postgres 17 server, and restored the dump there, to allow me to check and test that PG17 works with everything:-

    /usr/local/pgsql/bin/psql -d postgres -f ~/backups/20241209-PreUpgradeBackup.bak

But this would require a data loss period in production which could be unacceptable, and when upgrading postgres, it is also important to rebuild your replicas from the new instance.  This provides the opportunity to use it as a means to do a seamless, zero downtime upgrade - and it's also a good way to upgrade each server separately.  I also considered this as an opportunity to test my failover processes so I downed my master server from the pool [1] (so I can bring it back up if there's a problem with the upgrade) and ran this on a secondary server:-

    SELECT pg_promote(); 

Which makes the instance writeable, instantly failing over (using the connection string approach discused in my previous blog post on migrating to Postgres, the applications will now switch to writing to this location) - and checked everything runs smoothly for DR purposes, installed Postgres 17 on a new instance, configured the pg_hba.conf file to allow replication from the new-master, and ran:-

    pg_basebackup -h yourmasterservername.yourdomain.com -U replica_user -X stream -C -S yourreplicationslotname -v -R -W -D /var/lib/postgresql/17/data/