There are built-in options like logical replication and streaming replication. Streaming replication doesn’t give you 2-way replication or mirroring, so that’s out for me. This article doesn’t get into 2-way replication yet. I’ll add it later or write another article to add 2-way replication. Here is a detailed comparison between pglogical and logical replication. The biggest plus with pglogical for me is conflict resolution.

I had some challenges setting this up. The provided pglogical documentation is terse and makes a lot of assumptions about your knowledge.

My environment:

• PostgreSQL 12 (I don’t think pglogical has official support for 13 yet)
• Centos 8
• 2 instances in different locations for more separation and better redundancy. I’m going to use IP addresses 1.1.1.1 for instance 1 and 2.2.2.2 for instance 2. The machine you connect from is 9.9.9.9. Please change these accordingly.
• pglogical requires a superuser. I don’t like this, but it is what it is. The user I’m using is called appuser.

One major problem I had was trying to get pglogical running with the default PostgreSQL installation. You cannot use the RPM install for pglogical if you have PostgreSQL installed. You’ll have to install from source. Since my setup was new, I chose to reinstall PostgreSQL from the from the recommended repos. Here’s a line about this from official documentation.

To be able to install pglogical, these RPMs all require the PGDG PostgreSQL releases from http://yum.postgresql.org/. You cannot use them with stock PostgreSQL releases included in Fedora and RHEL.

sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum install postgresql12-server postgresql12-contrib

curl https://access.2ndquadrant.com/api/repository/dl/default/release/12/rpm | bash

yum -y install postgresql12-pglogical

This is the default data path for this install.

/var/lib/pgsql/12/data/

Add these lines in this file: /var/lib/pgsql/12/data/postgresql.conf

wal_level = ‘logical’

max_worker_processes = 10 # one per database needed on provider node

max_replication_slots = 10 # one per node needed on provider node

max_wal_senders = 10 # one per node needed on provider node

shared_preload_libraries = ‘pglogical’

track_commit_timestamp = on

pglogical.conflict_resolution = 'last_update_wins'

Enable trust login

This step is not essential, but it makes it simpler to manage locally. Change the following in /var/lib/pgsql/12/data/pg_hba.conf

local all  all           trust

host  all  127.0.0.1/32  trust

Allow external access

In /var/lib/pgsql/12/data/postgresql.conf set

listen_addresses = ‘*’

In /var/lib/pgsql/12/data/pg_hba.conf set

On instance 1:

host all all 2.2.2.2/32 md5 # other machine’s IP

host all all 1.1.1.1/32 md5 # this machine’s IP

host all all 9.9.9.9/32 md5 # your dev machine’s IP

On instance 2:

host all all 1.1.1.1/32 md5 # other machine’s IP

host all all 2.2.2.2/32 md5 # this machine’s IP

host all all 9.9.9.9/32 md5 # your dev machine’s IP

For some reason, the own machine’s IP is also required for this to work

Create user and database

On both instances, log in and create the user and databases.

psql -U postgres

CREATE USER appuser WITH PASSWORD 'apppass' WITH superuser;

CREATE DATABASE appdb WITH OWNER appuser;

Install CITEXT & pglogical

You can skip CITEXT — it allows for a case-insensitive text datatype, which I find very useful.

On both instances

psql -U postgres

\c appdb

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;

CREATE EXTENSION pglogical;

Set up provider

Instance 1

select pglogical.create_node(node_name := 'provider1', dsn := 'host=1.1.1.1 port=5432 dbname=appdb user=appuser password=apppass');

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

grant usage on schema pglogical to appdb;

Fix pg_current_xlog_location bug

On my setup, on instance 2, I encountered a bug where pglogical would call pg_current_xlog_location(). This has been replaced by pg_current_wal_lsn() since PostgreSQL 10. You can skip this step if you don’t run in to this bug.

CREATE FUNCTION pg_catalog.pg_current_xlog_location() RETURNS pg_lsn AS $$

BEGIN

RETURN pg_current_wal_lsn();

END;

$$ LANGUAGE plpgsql;

CREATE FUNCTION pg_catalog.pg_last_xlog_receive_location() RETURNS pg_lsn AS $$

BEGIN

RETURN pg_last_wal_receive_lsn();

END;

$$ LANGUAGE plpgsql;

Set up subscriber

Make sure you set up the table schema on instance 2 identical to instance 1 before you do the following. It will not work otherwise.

Instance 2

SELECT pglogical.create_node( node_name := ‘subscriber1’, dsn := ‘host=2.2.2.2 port=5432 dbname=appdb user=appuser password=apppass’ );

SELECT pglogical.create_subscription( subscription_name := ‘subscription1’, provider_dsn := ‘host=1.1.1.1 port=5432 dbname=appdb user=appuser password=apppass’ );

Check status

select subscription_name, status FROM pglogical.show_subscription_status();

Check replication completed

SELECT pglogical.wait_for_subscription_sync_complete(‘subscription1’);

Drop subscription

During the process of getting this running, I had to drop and recreate the subscription and nodes several times. Here are the commands for that, if you need to do it.

SELECT pglogical.drop_subscription(‘subscription1’);

Drop node

SELECT pglogical.drop_node(‘subscriber1’);

These tips are focused on US based positions. Tweak accordingly for your country.

- You don’t need a photo on your resume. Please remove it if you have one — this is not a modeling position.
- If you have a photo on your email profile, it will probably show up with your application. If you choose to keep the photo, it should be of yourself, by yourself. A smile is great, but limit it to that, don’t make funny faces. Keep it professional, you don’t have to wear a suit, but please do not wear a wife-beater (sleeveless undershirt) https://mic.com/articles/139771/why-are-we-still-calling-white-tank-tops-wife-beaters#.BlQiJSAHZ either. It should not include other people — even your significant other, child, dog or pet rock. Leave that for instagram.
- Use an email address that has your full name in it — funky ones are not only not particularly professional, but not easy to find when I’m searching for you later. Numbers after your name is fine, leave the cutesy email addresses for personal contacts. If your name is John Doe, a good email address would be john.doe.1007@gmail.com. A gmail tip: gmail ignores the dots in your email address, johndoe1007@gmail is identical to john.doe.1007@gmail.com.
- Make sure your email client uses your full name when sending emails.
- Remove irrelevant content from your resume, no body cares if you pushed shopping carts or waited tables before, it’s irrelevant to this position.
- Don’t list hobbies, passions, unless relevant to the job.
- I don’t care if your resume is one page or five, but it needs to be easy to read and scan for relevant information. Ideally you want a resume for each job type. Say you’re applying for an android developer job — don’t remove your web experience, but highlight the android experience, make my job easier.
- Make sure you’ve actually attached your resume.
- Make a serious effort to make your resume look presentable. No, you may not be applying for a designer job, but your job will reflect the work of a designer and an eye for detail is important. You don’t have to take design courses, but pick a decent typeface, don’t use Times News Roman, Arial and I shouldn't even have to mention this — Comic Sans. Use one and only one typeface — don’t mix multiple typefaces across the resume. Make sure your bullets and tables are aligned. Make sure there’s adequate spacing between sections. Make sure a section heading is not one page and the content on the next, move the whole section to the next page if this happens. The easiest thing to do is find an online template — search, there are many out there.
- Colors are OK, but don’t use hard to read or garish ones — unless you have a good eye for design, this might be better used from templates.
- Don’t title your resume with Resume, or Curriculum Vitae, I know what it is, put your full name there.
- Leave out the meaningless objective, instead put in a 2–5 line summary of yourself.
- Make sure your resume looks good when seen from the GMail viewer. Send it to yourself and see how it looks for your recipient. Some resumes get mangled and make you look bad. While you’re at it, make sure you test with the Microsoft Office Online viewer as well.
- You don’t need an address on your resume, but definitely include your city and state, email, phone and other contact info like linkedin, skype, etc, if you use it. If you’re applying for a position in another country, remote or otherwise, mention the country where you live.
- Make sure all links are clickable and work correctly.
- If you want to show case an app, try to put it on the app/play store. Try to have this under your name — it makes it obvious that it’s yours. Having this under a company name yours or otherwise, makes it harder to prove that it’s yours. This is far more likely to be looked at than looking at the source-code you have on github. If you are applying for a job in a different country, make sure it’s accessible from the target country. Don’t put screenshots within your resume, attach a separate document.
- Don’t use emojis, lol or smiley faces in your resume or cover letter
- Don’t refer to yourself in the third person — “Mr. So&So has created this wonderful app” on your resume and especially in your cover letter. It sounds ridiculous. Either be direct and say “I have created this wonderful app” or don’t refer to yourself at all and say “Created this wonderful app”.
- A cover letter is highly encouraged. Keep it brief — 3–5 lines. Mention directly related experience that would make you a good fit. Mention anything that makes you stand out positively. Don’t ramble, don’t mention irrelevancies. Cover letters should absolutely be targeted to the company and position you’re applying. Be careful not to mention the wrong company’s name in the cover letter.
- This one should be obvious, but you’d be surprised how many resumes I receive in foreign languages. If you’re applying for a job in the US, make sure your cover letter and resume are in English.