I've been trying to set up log-shipping replication between 2 CentOS 8 vagrant boxes, but I have two problems I just can't seem to find the answer for:
1.,I've followed the official pgsql documentation to set up continuous archiving on the master, but it doesn't archive any wal files, even though there are some of them in the pg_wal directory and their archive status is ready. It checked the logs and it does seem to try to archive them, but there's always an error.
This is the error for 'scp %p [email protected]:/test/%f':
scp: /test/000000010000000000000001: No such file or directory
2021-01-21 16:52:05.851 UTC [4635] LOG: archive command failed with exit code 1
2021-01-21 16:52:05.851 UTC [4635] DETAIL: The failed archive command was: scp pg_wal/000000010000000000000001 [email protected]:/test/000000010000000000000001
and this is the error for 'cp %p /test/%f' :
cp: cannot create regular file '/test/000000010000000000000001': No such file or directory
2021-01-21 16:52:09.415 UTC [5862] LOG: archive command failed with exit code 1
2021-01-21 16:52:09.415 UTC [5862] DETAIL: The failed archive command was: cp pg_wal/000000010000000000000001 /test/000000010000000000000001
In the first example I'm trying to scp wal files to my standby server and in the second one I'm simply trying to cp them into a test folder I created in the /var/lib/pgsql/13/data folder. I checked with ls -la and postgres should have permission to write in the folder: drwxr-xr-x. 2 postgres postgres. I've tried other archive_commands too, like the one in the documentation: 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f', and it logged the same error as the cp one above.
I've also checked the archiver, with select * from pg_stat_archiver; but it just showed that archivation failed a lot of times
Here are my posgresql.conf settings:
listen_addresses = '*'
max_connections = 100
password_encryption = scram-sha-256
shared_buffers = 128MB
dynamic_shared_memory_type = posix
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB
archive_mode = on
archive_command = '' #as mentioned above
archive_timeout = 60
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_line_prefix = '%m [%p] '
log_timezone = 'UTC'
datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
I've also changed pg_hba.conf to allow host to connect to the db, so I'll include that as well just in case:
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
host all all 0.0.0.0/0 trust
host all all ::1/128 scram-sha-256
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256
2., My other question is more related to linux, or possibly vagrant. Since I'm trying to set up log-shipping, I need the wal archive to be somewhere the standby can read the files, which is why I'm trying to scp the files over. I generated ssh key-pairs with ssh-keygen for both vagrant, which is the default user when I ssh into the box and postgres, and then added both public keys to both vagrant(/home/vagrant/.ssh) and postgres(/var/lib/pgsql/.ssh) users' authorized_keys list. The problem is that while I can ssh into the vagrant user on the standby server with both users from the primary, when I try ssh into postgres user with either it says [email protected]: Permission denied (publickey,gssapi-keyex,gssapi-with-mic).
I've tried checking what the problem might be using ssh -v [email protected] command and it works perfectly, up until the point the server should accept the key:
debug1: Offering public key: /home/vagrant/.ssh/id_rsa RSA SHA256:DgndkBcWnrG7q0VkLNUA+hjsHGO+HWyUQRTrffl8NWg
debug1: Authentications that can continue: publickey,gssapi-keyex,gssapi-with-mic
debug1: Trying private key: /home/vagrant/.ssh/id_dsa
debug1: Trying private key: /home/vagrant/.ssh/id_ecdsa
debug1: Trying private key: /home/vagrant/.ssh/id_ed25519
debug1: Trying private key: /home/vagrant/.ssh/id_xmss
debug1: No more authentication methods to try.
[email protected]: Permission denied (publickey,gssapi-keyex,gssapi-with-mic).
My question is why this might be, and if I even need to ssh into postgres user, or I can just simply save it somewhere with vagrant user and make that folder possible to read to postgres user somehow(I've read about maybe using shared folders, but I haven't found any good guides on how to do that yet).
Here are the vagrantfiles, in case they are needed:
primary:
Vagrant.configure("2") do |config|
config.vm.box = "generic/centos8"
config.vm.hostname = "pgsrv1"
config.vm.provider "virtualbox" do |vb|
vb.name = "pgsrv1"
vb.memory = "1024"
end
config.vm.network :forwarded_port, host: 5432, guest: 5432
config.vm.network "private_network", ip: "10.0.0.200"
end
standby:
Vagrant.configure("2") do |config|
config.vm.box = "centos8/standby"
#config.vm.box_url = "/vagrant/centos8_standby/package.box"
config.vm.hostname = "pgsrv2"
config.vm.provider "virtualbox" do |vb|
vb.name = "pgsrv2"
vb.memory = "1024"
end
config.vm.network :forwarded_port, host: 5434, guest: 5432
config.vm.network "private_network", ip: "10.0.0.201"
end
Sorry if they are really noob questions, but I'm fairly new to linux and vagrant and I've been stuck on this 2 weeks and nothing I find online seems to work at all.
Edit:
I made a completely new vagrantfile and after setting it up copied it with vagrant package and now archive command works as it should(still no clue what the problem is on the original box).
I configured ssh again however and it's still the same, I can easily ssh into the vagrant user and so I was able to scp the wal files over with archive_command, but still can't ssh into postgres.
So now my only question remains if anyone has any idea why I'm not able to ssh into postgres, or if there's a simple workaround, like making the folder I scp the wals into somehow readable to postgres user.
Edit1:
It turns out that it was selinux doing something magical, some configuration that allowed ssh conns to vagrant user and not to postgres, so the issue was solved by turning it off :).
question from:
https://stackoverflow.com/questions/65833063/setting-up-postgresql-13-log-shipping-replication-between-2-centos-8-vagrant-box 
