could not accept SSL connection: Success
Hi all, We've noticed the following connection error logs recently: sql_error_code = XX000 LOG: could not accept SSL connection: Success We're seeing this on PostgreSQL databases running on Ubuntu Focal 20.04 with different PostgreSQL versions (13.5, 13.4, 12.9, 10.19...). After going through the source code I think that this comes from a `SSL_ERROR_SYSCALL` where the errcode itself is 0, given the "Success" error that's getting logged. The server is accepting other (TLSv1.3) SSL connections from the same source around the same time when this happens, so I understand this error/behavior is likely due to the client itself closing the connection. I saw previous discussions where different errors were logged with the "Success" message and this was corrected/treated as a bug, but I couldn't find similar reports specific to "could not accept SSL connection". Is this a known issue or case? Regards, Carla
How to properly hash a row or set of columns.
Hashing a row or set of columns is useful in some circumstances where you need to compare the row with for instance incoming data. This is most relevant when you do not control the source data yourself, or else you would usually solve it by other means. Still, It would be great if you could do it like this: create table t ( a text b text hashid varchar not NULL GENERATED ALWAYS AS (sha256(row(a,b))) stored ) But, row is not immutable so you are not allowed to do this. Instead, you need to start concatenating columns, but if you want to do that correctly, you also need separator symbols between columns, which then needs to be escaped in the individual column values. And then you have to handle NULL properly as well. The first example handles all of this, if only row was immutable. Any better way of doing this? Can I create my own row2() constructor that IS immutable in a simple way? best regards Klaudie
Re: How to properly hash a row or set of columns.
A minor correction by casting the row a little: create table t ( a text b text hashid varchar not NULL GENERATED ALWAYS AS (sha256(row(a,b)::text::bytea)) stored ) Sent with [ProtonMail](https://protonmail.com/) Secure Email. ‐‐‐ Original Message ‐‐‐ On Tuesday, January 18th, 2022 at 14:34, Klaudie Willis wrote: > Hashing a row or set of columns is useful in some circumstances where you > need to compare the row with for instance incoming data. This is most > relevant when you do not control the source data yourself, or else you would > usually solve it by other means. > > Still, It would be great if you could do it like this: > create table t ( > a text > b text > hashid varchar not NULL GENERATED ALWAYS AS (sha256(row(a,b))) stored > ) > > But, row is not immutable so you are not allowed to do this. Instead, you > need to start concatenating columns, but if you want to do that correctly, > you also need separator symbols between columns, which then needs to be > escaped in the individual column values. And then you have to handle NULL > properly as well. The first example handles all of this, if only row was > immutable. > > Any better way of doing this? Can I create my own row2() constructor that IS > immutable in a simple way? > > best regards > > Klaudie
Re: WAL Archiving and base backup
Greetings, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Fri, Jan 14, 2022 at 11:31 AM Stephen Frost wrote: > > * Issa Gorissen (issa-goris...@usa.net) wrote: > > > Thx a lot. I thought about it but was not so sure about having a complex > > > script (compared to the very simple version when using the exclusive > > backup > > > - but this this is deprecated...). > > > > > > I will test your option with the simpler version and post it back to it > > can > > > maybe land in PostgreSQL documentation. > > > > The PG docs show how the command works and that's it. The commands > > in the docs aren't intended to be actually used in production > > environments. Writing a full solution involves having a good > > understanding of the PG code and how WAL archiving, backups, et al, are > > done. > > For all my suggestions of "use third-party where possible" I do think that > we should have, probably as part of pg_basebackup, a mode that performs a > filesystem copy of WAL to an archive location and verifies that the archive > is valid otherwise it write to the PostgreSQL log that there was a > problem. pg_basebackup should either be able to install that command (with > maybe some CLI prompts for settings or something) using ALTER SYSTEM. Our > documentation can then demonstrate the example usage of the archive_command > parameters as being a generic form of that command. We already have pg_receivewal, which is part of pg_basebackup, and is able to use a slot and such. I'm not sure that making pg_basebackup somehow also work as an archive command makes much sense- it's really intended to be a tool that's used remotely and that isn't going to work when being called out of archive_command. That is, if I'm on system B and doing pg_basebackup against system A, all of which happens using the PG replication protocol, how would pg_basebackup called out of archive_command be able to get the WAL over to system B..? Thanks, Stephen signature.asc Description: PGP signature
Re: WAL Archiving and base backup
On Tue, Jan 18, 2022 at 10:53 AM Stephen Frost wrote: > We already have pg_receivewal, which is part of pg_basebackup, and is > able to use a slot and such. I'm not sure that making pg_basebackup > somehow also work as an archive command makes much sense I suppose my proposal should have been: Create and document a new "PostgreSQL Server Application" [1] and name it: pg_archive_wal Advise people to set their archive_command to "pg_archive_wal --path=/location/of/archive %p/%f 1. https://www.postgresql.org/docs/current/reference-server.html Having pg_basebackup still prompt for permission to add that command to the system via ALTER SYSTEM (probably will some other logic) seems doable. Having created a base backup one still must decide on a wal archiving strategy. There appear to be two options, though as far as I can tell if one simply reads the documentation regarding backups they will not discover the pg_receivewal option. I, not knowing of that option myself, have been operating under the assumption that if one uses pg_basebackup that one would be required to setup an archive_command as well. The superior option is having a persistently running pg_receivewal command on a server. As noted above, the documentation does not do this option justice. The alternative option is to set archive_command; which at present is also poorly documented. My proposal above simply tries to improve on this. And while that is a good and easy starting point if there is agreement on pg_receivewal being a superior archiving option (leaving archive_command unset) reworking the documentation to guide the inexperience PostgreSQL DBA toward a "minimal but effective" backup procedure is needed. David J.
Re: WAL Archiving and base backup
Greetings, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Tue, Jan 18, 2022 at 10:53 AM Stephen Frost wrote: > > We already have pg_receivewal, which is part of pg_basebackup, and is > > able to use a slot and such. I'm not sure that making pg_basebackup > > somehow also work as an archive command makes much sense > > I suppose my proposal should have been: > > Create and document a new "PostgreSQL Server Application" [1] and name it: > pg_archive_wal > Advise people to set their archive_command to "pg_archive_wal > --path=/location/of/archive %p/%f > > 1. https://www.postgresql.org/docs/current/reference-server.html Or just link to a proper solution that already exists and includes support for object stores, multiple repositories, checksums all files and stores them in a manifest to validate them ... > Having pg_basebackup still prompt for permission to add that command to the > system via ALTER SYSTEM (probably will some other logic) seems doable. It doesn't seem to follow, to me anyway, that if you're using pg_basebackup that you'd want to use this proposed pg_archive_wal. Much more likely would be that you'd want to use pg_receivewal, which doesn't need any archive command to be set. Seems like a feature in need of a use case is what I'm getting at here. > Having created a base backup one still must decide on a wal archiving > strategy. There appear to be two options, though as far as I can tell if > one simply reads the documentation regarding backups they will not discover > the pg_receivewal option. I, not knowing of that option myself, have been > operating under the assumption that if one uses pg_basebackup that one > would be required to setup an archive_command as well. Adding a reference to pg_receivewal under the continuous archiving section would probably make sense, similar to how pg_basebackup is referenced from the base backup section. Might even make sense to have a 'low level API' section for WAL archiving which mentions archive_command ... or maybe not and rip out the existing 'low level API' section as it really isn't nearly detailed enough for someone to be able to write a proper tool and having it there implies that it does provide that. > The superior option is having a persistently running pg_receivewal command > on a server. As noted above, the documentation does not do this option > justice. Not sure that I'd say that it's the superior option, but it depends on the options that are being considered and if you're limiting those to "just what exists in core." > The alternative option is to set archive_command; which at present is also > poorly documented. My proposal above simply tries to improve on this. And > while that is a good and easy starting point if there is agreement on > pg_receivewal being a superior archiving option (leaving archive_command > unset) reworking the documentation to guide the inexperience PostgreSQL DBA > toward a "minimal but effective" backup procedure is needed. Adding a new PG server application isn't exactly what I'd call just a simple improvement to the documentation ... The existing documentation for base backups does, in fact, link to pg_basebackup to guide the new DBA to a solution for base backups that's minimal but effective. Doing PITR is getting beyond just the minimal, but even so, linking to pg_receivewal in a similar manner probably does make sense since we link to pg_basebackup, so I think I agree with you about that specific change. Hopefully, we can remove the long deprecated exclusive backup option and further simplify the backup documentation. Thanks, Stephen signature.asc Description: PGP signature
Re: could not accept SSL connection: Success
On Mon, Jan 17, 2022 at 05:05:52PM +0100, Carla Iriberri wrote: > I saw previous discussions where different errors were logged with the > "Success" > message and this was corrected/treated as a bug, but I couldn't find similar > reports specific to "could not accept SSL connection". Is this a known > issue or > case? Not based my recent mailing list memories, but I may be running short. The error comes from the backend as you say, where this log would expect something in saved_errno to feed %m. And, upstream documentation tells that: https://www.openssl.org/docs/manmaster/man3/SSL_get_error.html "On an unexpected EOF, versions before OpenSSL 3.0 returned SSL_ERROR_SYSCALL, nothing was added to the error stack, and errno was 0. Since OpenSSL 3.0 the returned error is SSL_ERROR_SSL with a meaningful error on the error stack." This would mean that relying on %m would be wrong for this case. And I guess that you are using a version of OpenSSL older than 3.0? -- Michael signature.asc Description: PGP signature
How to schedule running of a script?
Can a script be scheduled to run within Postgres? Regards, David
Re: How to schedule running of a script?
On Tuesday, January 18, 2022, Shaozhong SHI wrote: > Can a script be scheduled to run within Postgres > Core, no. I think there are some extensions you could search for. e.g., pg_cron and pgagent IIRC. David J.
