[GENERAL] Synchronous Commit, WAL archiving and statement_timeout
Hello, I noticed something strange and I would like to understand what's happening. I have the following environment: - 2 PostgreSQL instance running in hot-standby with synchronous commit activated. (further called Master & Slave) - The archiving of the WAL files is activated on the master running every 5 minutes - Slave is down I set the statement_timeout inside my ~/.psqlrc: $ cat ~/.psqlrc set statement_timeout = 1; commit; When running an UPDATE statement (via psql) on the master, it hangs (psql seems to wait a response from the slave) ignoring any value I set in my .psqlrc and the update is done and written on the Master. Furthermore if I try (doing the same things in a small script with a timeout on the call to PQexec) to make a call to PQcancel it does nothing on the Master. I expected the statement to timeout because the synchronous_commit wouldn't work since the Slave is down while rollbacking on the Master.
Re: [GENERAL] Synchronous Commit, WAL archiving and statement_timeout
Original Message Subject: Re: [GENERAL] Synchronous Commit, WAL archiving and statement_timeout Local Time: 3 février 2017 1:15 AM UTC Time: 3 février 2017 00:15 From: adrian.kla...@aklaver.com To: JP Jacoupy , pgsql-general@postgresql.org On 02/02/2017 09:15 AM, JP Jacoupy wrote: > > Hello, > > I noticed something strange and I would like to understand what's > happening. > > I have the following environment: > - 2 PostgreSQL instance running in hot-standby with synchronous commit > activated. There have been many changes in replication over the years/versions, so it would be helpful to know what Postgres version you are using? Sorry, forgot to specify. I'm running Postgres 9.4.4 under CentOS 6.6 > (further called Master & Slave) > - The archiving of the WAL files is activated on the master running > every 5 minutes > - Slave is down > > I set the statement_timeout inside my ~/.psqlrc: > $ cat ~/.psqlrc > set statement_timeout = 1; > commit; > > When running an UPDATE statement (via psql) on the master, it hangs > (psql seems to > wait a response from the slave) ignoring any value I set in my .psqlrc > and the update is > done and written on the Master. Furthermore if I try (doing the same > things in a small > script with a timeout on the call to PQexec) to make a call to PQcancel > it does nothing > on the Master. > > I expected the statement to timeout because the synchronous_commit > wouldn't work https://www.postgresql.org/docs/9.4/static/warm-standby.html#SYNCHRONOUS-REPLICATION "25.2.8.3. Planning for High Availability Commits made when synchronous_commit is set to on or remote_write will wait until the synchronous standby responds. The response may never occur if the last, or only, standby should crash." > since the Slave is down while rollbacking on the Master. As I understand this, the commit on the master should wait the response from the slaves and might come under the hammer of the statement_timeout. Is there anything I could do to prevent this hang (except switching to asynchronous commit)? -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] Service configuration file and password security
Hello, This might seem a pretty novice question but I can't find an answer. Can the password be stored in an encrypted way inside a service configuration file? -- Jacoupy Jean-Philippe Sent from [ProtonMail](https://protonmail.ch), encrypted email based in Switzerland.
Re: [GENERAL] Handling psql lost connections
Not a response to OP but this also occurs with libpq connections. The only thing I found at the moment is to lower the net.ipv4.tcp_retries2 value to 8 (instead of 15). This will lower the TCP timeout from kernel to around 100 seconds instead of 15 minutes. Sent from ProtonMail mobile Original Message On 29 mars 2017 à 18:05, Adrian Klaver wrote: On 03/29/2017 08:49 AM, Steve Crawford wrote: > When firewalls/VPNs stand between my psql client and a remote PostgreSQL > server the connection will on occasion time out and drop. This results > in the following scenario: > > -Leave for lunch mid project - leave psql open. > > -Return from lunch, complete and submit large query. > > -Notice query is taking too long. cancel it. > > -Cancel doesn't return - realize that connection has dropped. > > -Kill psql - history is not written out. Start query from scratch. > > Is there: > > 1) A way to set psql to send keepalives? From server side: https://www.postgresql.org/docs/9.6/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS tcp_keepalives* I guess you could abuse \watch: https://www.postgresql.org/docs/9.6/static/app-psql.html \watch [ seconds ] Repeatedly execute the current query buffer (as \g does) until interrupted or the query fails. Wait the specified number of seconds (default 2) between executions. Each query result is displayed with a header that includes the \pset title string (if any), the time as of query start, and the delay interval. aklaver@test=> \watch 2 Watch every 2s Wed Mar 29 08:59:55 2017 ?column? -- 1 (1 row) Watch every 2s Wed Mar 29 08:59:57 2017 ?column? -- 1 (1 row) With a larger value of seconds. > > 2) A way to gracefully kill psql ensuring that the history is saved? > > Yes, I know I and my coworkers could spend brain cycles trying to > unerringly remember to close and restart connections, write all queries > in an external editor and then submit them, etc. but I'm looking for > more user friendly options. Use the internal editor(\e)? > > Cheers, > Steve -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to upload backups
Hi, Seems to me your role "ronb" doesn't have the rights to create schema on your database. Please refer to the GRANT command in the documentation. https://www.postgresql.org/docs/9.0/static/sql-grant.html Sent from [ProtonMail](https://protonmail.ch), encrypted email based in Switzerland. Original Message Subject: [GENERAL] Unable to upload backups Local Time: 19 avril 2017 12:56 PM UTC Time: 19 avril 2017 10:56 From: ronb...@walla.co.il To: pgsql-general@postgresql.org Hi, I'm using PostgreSQL 9.3.2 I'm running the command: psql -h testserver -U ronb -f backup.sql -q -d foldertest 2>error.txt >output.txt This should generate my database in foldertest However this doesn't work. It's unable to create schemas in the error.txt i see "permission denied for database foldertest". I know this is not an access permission issue because there is a public schema which is buildin and it does create the tables/data in there. It just cant create new schemas. The intresting thing is that if I do: psql -h testserver -U postgres -f backup.sql -q -d foldertest 2>error.txt >output.txt Everything works. It create all schemas and generate the database correctly. I don't see any diffrent in the hba.conf between postgres and ronb users. What can be the problem?
