Issues connecting to remote server

[johnaohara]

Hey @jesperpedersen hope you are doing well!!

I have made a couple of changes to our setup and ran into some problems, I hope you can point me in the correct direction!

The main change has been to try running pgmoneta to backup a remote postgres instance, i.e. not an instance on localhost

After changing the config to change the hostname (and making sure the repl user could connect from the remote machine etc) I was seeing the following message in the pgmoneta logs;

Server primary need wal_level at replica or logical

To bring our install up to date, I rebuilt pgmoneta from the current main branch and restarted, but now I am seeing when I try to start;

2024-01-29 05:27:48 FATAL main.c:1882 Could not get version for server primary

I have checked the version query : (https://github.com/pgmoneta/pgmoneta/blob/main/src/libpgmoneta/server.c#L271) using a remote log in for the repl user and it is working as expected;

postgres=> SELECT split_part(split_part(version(), ' ', 2), '.', 1);
 split_part 
------------
 16
(1 row)

So questions...

1 - There is no info about why the server has failed validation, so idk what i need to change/look for to rectify. Any ideas?
2 - any pointers as to why the version check query failed? could this be a bug?

Many Thanks :)

[Jubilee101]

Hi @johnaohara ,

For 1, perhaps you should try checking the wal_level in postgresql.conf, please see details here.

For 2, it is a little bizarre. I think you are connecting to the server just fine, otherwise it won't start querying the server in the first place. For pgmoneta_server_get_version() to return error code 1, most likely something is wrong with pgmoneta_query_execute(), and/or you are getting NULL query_response. Could you check in which step inside pgmoneta_query_execute() pgmoneta entered error section?

[Jubilee101]

On second thought it's unlikely to be an error response, we would've caught it here:

pgmoneta/src/libpgmoneta/message.c

Line 1081 in 0bbebed

if (pgmoneta_has_message('E', data, data_size))

[johnaohara]

It is returning warning messages, before returning the result. I am attempting to capture the packets on the remote machine with TCP dump to see what postgres is sending across the wire, but it's been a while since I have done that remotely

[Jubilee101]

Thank you so much. I'll wait for the update. Is there anything on postgres' log that's worth noticing?

[johnaohara]

This is the best I can do atm: https://gist.github.com/johnaohara/ea65c0b13fab28f0e05f1b5d5eff04e9

On the server, I see the same warnings that is sent accross the wire:

25985 [2024-01-31 01:13:17.540 EST] [] [0] WARNING:  database "postgres" has a collation version mismatch
25985 [2024-01-31 01:13:17.540 EST] [] [0] DETAIL:  The database was created using collation version 2.28, but the operating system provides version 2.36.
25985 [2024-01-31 01:13:17.540 EST] [] [0] HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

I will try updating the collation on the remote machine

[Jubilee101]

Hmm, seems that this error response message is not picked up by pgmoneta. It should've gone to error before pgmoneta_extract_message_from_data. And we should definitely parse the message into log so that you wouldn't have to gone through any of this.

Anyway, let me know if that solves the problem. I'll do some further checking and add more informative logs.

[johnaohara]

It should've gone to error before pgmoneta_extract_message_from_data

The server is actually returning the response on the wire. maybe the response buffer was not big enough, given the warning messages that were returned? in a normal scenario, you would not have the warnings returned

And we should definitely parse the message into log so that you wouldn't have to gone through any of this.

This would help a lot to track down the issue

[johnaohara]

It looks like there are multiple messages returned, and pgmoneta is only inspecting the first message returned on the socket, but the actual correct result is in a later message

[Jubilee101]

The server is actually returning the response on the wire. maybe the response buffer was not big enough, given the warning messages that were returned? in a normal scenario, you would not have the warnings returned

Yes, that's a good point. I'll talk with @jesperpedersen to see how to fix this. We have new stream buffer that's more adaptive. And we should definitely skip warning messages.

This would help a lot to track down the issue

Will have that in the near future!

It looks like there are multiple messages returned, and pgmoneta is only inspecting the first message returned on the socket, but the actual correct result is in a later message

Yeah, probably need some good fixes in the query execution functionality. We'll submit a patch as soon as possible.

Thank you so much, and so sorry for the trouble. I assume updating the collation fixed the problem?

[johnaohara]

Thanks a lot for your help!