How do I install pglogical extension on Windows machine?

Question

I need to migrate an enterprise production database from a Windows source machine running Postgres 9.5 to an Ubuntu destination machine running Postgres 11.6 with < 15 mins downtime. I plan to do this with pglogical, which requires the extension being loaded on both source and destination. I am having trouble with the source side because it is Windows.

I have very little Postgres-Windows experience and can neither find any helpful literature on the specific situation nor can I figure out for myself how to presumably install from source.

Answer 1

If your your Postgres version is at least 10, then you don't need to install an extension on Windows. Just follow the official replication guide for the logical version from the Postgres wiki.

• https://www.postgresql.org/docs/current/logical-replication-quick-setup.html
• https://www.postgresql.org/docs/current/logical-replication.html

If the Postgres is 9.6 or below, you will need to compile it from source

Answer 2

I've dug and dug and so far the only answer I've been able to find is "ugh... Windows". It seems like the only way to build from source is using Visual Studio, which I don't have or know how to use.

Sources:

https://www.2ndquadrant.com/en/blog/compiling-postgresql-extensions-visual-studio-windows/

https://postgrespro.ru/list/thread-id/1835275

Answer 3

Alex, the 2ndQuadrant article you link to in your own comment solves this. A few of the project or build options noted there have moved a little bit in the newer VisualStudio Community editions, so I can see where you got hung-up.

Just for kicks I compiled 32-bit DLL on my oldest Windows instance. I included the /Release path so you can grab my DLL & see if it works for you. It's (a) 32-bit because I'm assuming worst-case for an old v9.5 install, and (b) targets Postgres 9.6 because that's what I had installed. Unless there were major API changes though, it should connect to v9.5 without any issues:

• https://github.com/mbijon/winpglogical/tree/master

If you find you need a version that entirely matches Postgres 9.5, grab my solution files & VS Community 20xx. Load the project & update the Additional Include Directories in Project Config to target your v9.5 paths. That should be all that's needed to link v9.5 Postgres libs.