Setting up Postgres hot standby in AWS

Question

We have a Posrgres 9.5 database running on an EC2 in AWS and we're trying to set up a hot standby. The server has around 3TB of data, so backing up is not a quick task.

I initially tried to create a base backup for the hot standby by running (following this guide):

sudo -u postgres pg_basebackup -h <primary IP> -D /data/postgres -U repuser -v -P --xlog-method=stream

After letting it run for a few hours, I determined it was going to take it almost two weeks to complete the base backup. I was worried that the primary server wouldn't have WAL logs dating back 14 days by the time it finished, so I tried an alternate method.

I shutdown the primary server overnight and took a snapshot of the EBS volume. (We store the data directory on a separate volume.) I then created a volume from the snapshot and attached it to the standby.

At this point, both the primary and standby servers are shutdown, and they both have matching data directories. I fired up the primary, it obviously came online no problem. The standby however, did not. It took a little digging, but I found this error in the log folder (csv):

"hot standby is not possible because wal_level was not set to ""hot_standby"" or higher on the master server",,"Either set wal_level to ""hot_standby"" on the master, or turn off hot_standby here."

The primary server's wal_level IS set to hot_standby. I also found online that this error could happen if the standby server simply can't connect to the primary, but I've verified the credentials and they're fine. (They also worked fine when I was trying to use pg_basebackup.)

So here's my question:

1) Why didn't taking a snapshot of the primary and mounting it to the standby work? Is there a way to make it work?

2) If not, is there a faster way to do a base backup?

3) If not, will the primary still have enough WAL data after the backup finishes in 14 days? In other words, will the hot standby be able to catch up on what its missed while running pg_basebackup?

Answer 1

Luke, the message says: ", or turn off hot_standby here."

Is not the case, in your stand by server (as it came from a snapshot)?

I suppose you don't want to use rds, wright?

If it doesn't do the job, I don't have a simple answer. It will depend on the acceptable downtime window you can have, whether this is a single application or a multi tenant one,...

You could try:

1) to use pg_barman; 2) dump structure first and then try to dump each table separately so you can take advantage of using more cores 2.1) in this scenario the limitation would be io 2.2) I'll need more time to analyze FK use in order to build a right sequence 2.3) in this case I would recommend rename the database for a while, so no new data will be created 3) to shard your database

Good luck