PostgreSQL on a Raspberry Pi 3 64-bit with Binary Streaming Replication

In my home office I run PostgreSQL with streaming binary replication between two servers. I don’t need streaming replication in my home office, but it is a good way to learn the system and it’s also nice having the ability to switch which server is the master during upgrades. After a hardware failure in my VM server I decided it was really stupid to have both the master and slave on the same physical machine. My database needs are small as I only use mediawiki, zabbix, and a few other minor things so I decided to look at using a Raspberry Pi 3 in 64-bit mode as a database server. I was also curious if PostgreSQL could replicate between two different architectures: aarch64 (arm 64-bit) and amd64 (x86_64) as the endianness is the same and the bitness is as well. I’m always on the lookout for new projects involving Raspberry Pis, Linux, and other related things and thought this might be a fun thing to try. I run Gentoo on all of my Linux machines and I also refuse to use SystemD and instead use OpenRC which means you’ll need to adjust a few commands here and there if you use something else!

There is a Gentoo guide that covers installing on the Raspberry Pi 3 in 64-bit mode and is a separate beast from the normal install guide for other Raspberry Pis due to complications with getting the Pi3 into 64-bit mode.

Once you have your Pi3 up and running the first we need is to keyword the version of PostgreSQL we want to use by creating a file in /etc/portage/package.keywords/postgresql:

=app-eselect/eselect-postgresql-1.2.1 ~arm64
=dev-db/postgresql-9.5.5 ~arm64

Next we want to select the use flags we want to incorporate:

dev-db/postgresql nls pam python readline server ssl threads zlib

Gentoo has a great quickstart guide that you should look over before continuing to see if you need any additional settings.

Then install PostgreSQL as you normally would:

emerge -av =dev-db/postgresql-9.5.5

On my setup I have a slightly different datadir than the Gentoo default due to migration(s) from other distros and systems, so I had to edit /etc/conf.d/postgresql and change the line ‘DATA_DIR=”/var/lib/postgresql/9.3/data’ to ‘DATA_DIR=”/var/lib/postgresql/9.3/main”‘. If you are doing a fresh install you will not likely need to change this.

Now we need to initialize the database:

emerge –config dev-db/postgresql:9.5.5

If you need to make changes to the default configs, now is the time to do so. For me I had existing configs to copy over.

For streaming replication I followed the official streaming replication guide and official binary replication guide to get it all up and running on my previous setup.

Finally, we will set postgresql to start at boot and start the service:

rc-update add postgresql-9.5 default

service postgresql-9.5 start

We can check the status on the master with the following psql command:

postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
——-+———-+————-+——————+—————-+—————–+————-+——————————-+————–+———–+—————+—————-+—————-+—————–+—————+————
30872 | 19764 | replication | walreceiver | 192.168.13.203 | | 38556 | 2017-04-12 03:52:50.486721-07 | | streaming | 15C/A80133C0 | 15C/A80133C0 | 15C/A8012E38 | 15C/A8012E38 | 0 | async
(1 row)

That’s about all there is to it!

Advertisements