Re: [GENERAL] Creating a user for pg_start_backup
On Tue, 2015-07-21 at 03:00 -0700, John R Pierce wrote: On 7/21/2015 1:51 AM, Andrew Beverley wrote: Thanks John. The backup script is running as root, so presumably I'd have to use sudo? Or should I run a separate cron job as postgres to do the above, and run the backup script separately? those are both possibilities. I'd either use su (not sudo) from root, or I'd cron it from the postgres DBA account, depending. Sorry to be dragging this off-topic, but what's the reason for using su instead of sudo? Thanks, Andy -- 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] Setting up HA postgresql
Hello Aviel, On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila avie...@gmail.com wrote: How can I set a highly available postgresql in a share-nothing architecture? I suggest you review the official documentation on high-availability configurations linked below: http://www.postgresql.org/docs/current/static/high-availability.html The most common configuration is to use PostgreSQL's built in master/standby streaming replication. However you will need to develop any fail-over logic you need yourself or use a third party tool such as EnterpriseDB's Failover Manager. *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila avie...@gmail.com wrote: Hey, How can I set a highly available postgresql in a share-nothing architecture? Best regards, Aviel B.
Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE
Hi Rafal: On Tue, Jul 21, 2015 at 12:43 PM, Rafal Pietrak ra...@ztk-rp.eu wrote: W dniu 21.07.2015 o 09:34, Francisco Olarte pisze: In this case I think you are mixing vouchers with voucher-numbers. IMO you could get a better dessign by using an auxiliary table and not nullifying the number after been consumed. Having only 6 digits I Hmmm. I don't think so. The application just needs to create a unique (for a period of time) number, and consume it at certain point. Everything else would be implementation burden, which should be kept to minimum. I see your points, totally opposite opinions, so no point in discussing it, discard my sugestions as not aplicable SVP. This looks a bit like an overkill for the above examples. It certainly is for your style of dessign, workng target, discard it. But I have other thoughts on the use of cryptographic sequences here. I wouldn't call it that, its misleading. It's just encrypted sequences. It has the pitfall of being sensitive to out-of-the-sequence poisoning, I mean: When another instance of an application starts issuing another sequence of vouchers, at certain point those sequences collide and applications despite using guaranteed lack of collisions will have a collision. Well, if you have aplication instance specific sequences, of course you have. But in this case even plain unencrypted sequences hae them. So the application *will have to have* a re-issuing of an INSERT implemented anyway. Of course, because the only point of using instance specific sequences instead of serial like you normally do must be having the possibility of collisions to justify a the existence of a re-issuing code and exercise it. If so, the whole point of using cryptographic sequence is missing. No. The whole point of using a global sequence ( in the db ) is avoiding collisions, be it encrypted or plain. The whole point of using crypto is to make it look random. If you use an application specific cryptographic sequence is because you want colisions ( success, as told above ) which looks random ( success too ). If you do not want colisions, use a global sequence. So, even though this collision is not statistically significant, but just its possibility results in that application have to take care of re-issuing of an INSERT. I use to tell people there are three meaninful cardinalities in computing, zero, one and many. And three probabilities ( NOT possibilities ), zero, one and other==(0,1). Except in some lucky domains you have to trat every (0,1) probability as been possible ( in fact my three probability values map nicely to impossible, possible and certain ). Using database.sequence() function to seed the cypher is not secure enough. What are you talking about? Where did you get that seeding idea? You do not seed the cipher, you use the ciphered sequence as voucher. In fact I've done this with session ids. I use a sequence for the ID and send the user the ciphered val. When it comes back I just decipher it and search. I did not have your 6-digit problems, so I just used 128 bit blocks, and it worked nicely. And I did not have any ciphered data in the DB. On the other hand, the ON CONFLICT RETRY has a nice feature for an application programmer (like myself) that it leaves us free of the implementation of the re-issue of an INSERT. One database-schema designer does that for all of us. But knowing if that usage scenario is too rare to match the heavy lifting the implementation required, is beyond my experience. Saying OCR is a nice feature is like saying MAGIC RAINBOW OVERDRIVE is a nice feature for a car. It does not exist, and nobody has described it with enough detail so people can assses its usefulness or implementation difficulty. A careful definition of the corner case will be needed. And even with it you have the possibility of inifinite colisions ( either due to generating too many 'vouchers' or to sheer bad luck ( like collisions among application instances ). If you try to write a nicely wrapped up description of the functionality maybe someone could see the usefulness and implement it, but I think this is possible but unlikely. Francisco Olarte. -- 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] Setting up HA postgresql
hey will, Is there any open-source tool instead of developing the fail-over logic by myself? 2015-07-21 18:34 GMT+03:00 William Dunn dunn...@gmail.com: Hello Aviel, On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila avie...@gmail.com wrote: How can I set a highly available postgresql in a share-nothing architecture? I suggest you review the official documentation on high-availability configurations linked below: http://www.postgresql.org/docs/current/static/high-availability.html The most common configuration is to use PostgreSQL's built in master/standby streaming replication. However you will need to develop any fail-over logic you need yourself or use a third party tool such as EnterpriseDB's Failover Manager. *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila avie...@gmail.com wrote: Hey, How can I set a highly available postgresql in a share-nothing architecture? Best regards, Aviel B.
Re: [GENERAL] Cannot reach pg server using -h localhost
Use netstat to look what IP postgres actually binds to. Nothing unusual: ~ netstat -an | grep 5432 tcp4 0 0 *.5432 *.*LISTEN tcp6 0 0 *.5432 *.*LISTEN 9767b1c9fd5d8ab1 stream 0 0 9767b1ca01d63a21 000 /tmp/.s.PGSQL.5432 and if you have it listen to 127.0.0.1, you see 127.0.0.1 in netstat? and psql -h 127.0.0.1 still fails? is lo0 still there (ifconfig)? Bye, Chris. -- 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] Setting up HA postgresql
Implementing failover logic is quite simple, and probably even preferred over a pre-built solution because you can implement it in the way that integrates well with your architecture. The basic logic is as follows: - On the standby server you would configure the trigger file. When you create a trigger file with the name you specify the standby will promote to master. - You have your queries send to the master. - If your application starts failing to connect to the master you would have logic evaluate why - When the master goes down you should - Generate a trigger file to promote the standby to master - Modify your DNS entries to have your applications point to the new master *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 12:20 PM, Aviel Buskila avie...@gmail.com wrote: hey will, Is there any open-source tool instead of developing the fail-over logic by myself? 2015-07-21 18:34 GMT+03:00 William Dunn dunn...@gmail.com: Hello Aviel, On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila avie...@gmail.com wrote: How can I set a highly available postgresql in a share-nothing architecture? I suggest you review the official documentation on high-availability configurations linked below: http://www.postgresql.org/docs/current/static/high-availability.html The most common configuration is to use PostgreSQL's built in master/standby streaming replication. However you will need to develop any fail-over logic you need yourself or use a third party tool such as EnterpriseDB's Failover Manager. *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila avie...@gmail.com wrote: Hey, How can I set a highly available postgresql in a share-nothing architecture? Best regards, Aviel B.
Re: [GENERAL] Cannot reach pg server using -h localhost
On 07/21/2015 10:09 AM, Fritz Meissner wrote: Hi, From the terminal in Mac OS X Yosemite, connecting to homebrew installed 9.4.4, I have a problem if I specify the host (I know this comes up all the time, bear with me as I have done a fair amount of digging already). This works: psql -Umyuser -d mydb sql (9.3.5) Type help for help. myuser=# Server log shows: [unknown] LOG: connection received: host=[local] myuser [local]LOG: connection authorized: user=myuser database=myuser So that's all good. If I use -h it doesn't work: psql -Umyuser -d mydb -h localhost psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Server log: [unknown] LOG: connection received: host=::1 port=51186 (... and that's it, no connection authorized line) So what happens if you do?: psql -U myuser -d mydb -h 127.0.0.1 I've checked the normal things people suggest, e.g. postgresql.conf: listen_addresses = 'localhost' # also '*', '127.0.0.1', '::1' port = 5432 and pg_hba.conf: # IPv4 local connections: hostall all 127.0.0.1/32trust hostall all localhost trust # IPv6 local connections: hostall all ::1/128 trust And psql and pg_ctl are pointing to the same installations: ~ which psql /usr/local/bin/psql ~ ll /usr/local/bin/psql /usr/local/bin/psql - ../Cellar/postgresql/9.4.4/bin/psql ~ which pg_ctl /usr/local/bin/pg_ctl ~ ll /usr/local/bin/pg_ctl /usr/local/bin/pg_ctl - ../Cellar/postgresql/9.4.4/bin/pg_ctl People have suggested checking for stuff on my machine that could close connections. The Mac OS firewall is off and so is pf (Mac OS packet filter). This is all after upgrading (I had the same problem with 9.3.5) and reinstalling. Anyone got more clues for me? Regards, Fritz -- 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
[GENERAL] Cannot reach pg server using -h localhost
Hi, From the terminal in Mac OS X Yosemite, connecting to homebrew installed 9.4.4, I have a problem if I specify the host (I know this comes up all the time, bear with me as I have done a fair amount of digging already). This works: psql -Umyuser -d mydb sql (9.3.5) Type help for help. myuser=# Server log shows: [unknown] LOG: connection received: host=[local] myuser [local]LOG: connection authorized: user=myuser database=myuser So that's all good. If I use -h it doesn't work: psql -Umyuser -d mydb -h localhost psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Server log: [unknown] LOG: connection received: host=::1 port=51186 (... and that's it, no connection authorized line) I've checked the normal things people suggest, e.g. postgresql.conf: listen_addresses = 'localhost' # also '*', '127.0.0.1', '::1' port = 5432 and pg_hba.conf: # IPv4 local connections: hostall all 127.0.0.1/32trust hostall all localhost trust # IPv6 local connections: hostall all ::1/128 trust And psql and pg_ctl are pointing to the same installations: ~ which psql /usr/local/bin/psql ~ ll /usr/local/bin/psql /usr/local/bin/psql - ../Cellar/postgresql/9.4.4/bin/psql ~ which pg_ctl /usr/local/bin/pg_ctl ~ ll /usr/local/bin/pg_ctl /usr/local/bin/pg_ctl - ../Cellar/postgresql/9.4.4/bin/pg_ctl People have suggested checking for stuff on my machine that could close connections. The Mac OS firewall is off and so is pf (Mac OS packet filter). This is all after upgrading (I had the same problem with 9.3.5) and reinstalling. Anyone got more clues for me? Regards, Fritz -- 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] Setting up HA postgresql
If you dig deeper into pgpool-II you will find that it does not have failover logic. Its intention is to pool connections and distribute query load among replicas, but it cannot differentiate node failure from network partition and cannot promote a standby to master in the case of failure. *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 1:55 PM, Aviel Buskila avie...@gmail.com wrote: Can you link me up to a good tutorial using pgpool-II? 2015-07-21 20:02 GMT+03:00 Joshua D. Drake j...@commandprompt.com: On 07/21/2015 08:34 AM, William Dunn wrote: Hello Aviel, On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila avie...@gmail.com mailto:avie...@gmail.com wrote: How can I set a highly available postgresql in a share-nothing architecture? I suggest you review the official documentation on high-availability configurations linked below: http://www.postgresql.org/docs/current/static/high-availability.html The most common configuration is to use PostgreSQL's built in master/standby streaming replication. However you will need to develop any fail-over logic you need yourself or use a third party tool such as EnterpriseDB's Failover Manager. Or use already available open source tools such as Pgpool-II or Linux-HA. Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you.
Re: [GENERAL] Setting up HA postgresql
On 07/21/2015 08:34 AM, William Dunn wrote: Hello Aviel, On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila avie...@gmail.com mailto:avie...@gmail.com wrote: How can I set a highly available postgresql in a share-nothing architecture? I suggest you review the official documentation on high-availability configurations linked below: http://www.postgresql.org/docs/current/static/high-availability.html The most common configuration is to use PostgreSQL's built in master/standby streaming replication. However you will need to develop any fail-over logic you need yourself or use a third party tool such as EnterpriseDB's Failover Manager. Or use already available open source tools such as Pgpool-II or Linux-HA. Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] Cannot reach pg server using -h localhost
On 07/21/2015 10:40 AM, Fritz Meissner wrote: CCing list On Tue, Jul 21, 2015 at 7:30 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: So what happens if you do?: psql -U myuser -d mydb -h 127.0.0.1 Same thing. Pause for about 30 seconds and then server closed the connection unexpectedly. Server log just shows: [unknown] LOG: connection received: host=127.0.0.1 port=51919 So what is in your hosts file? -- 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] Cannot reach pg server using -h localhost
Use netstat to look what IP postgres actually binds to. Nothing unusual: ~ netstat -an | grep 5432 tcp4 0 0 *.5432 *.*LISTEN tcp6 0 0 *.5432 *.*LISTEN 9767b1c9fd5d8ab1 stream 0 0 9767b1ca01d63a21 000 /tmp/.s.PGSQL.5432 -- 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] Setting up HA postgresql
Can you link me up to a good tutorial using pgpool-II? 2015-07-21 20:02 GMT+03:00 Joshua D. Drake j...@commandprompt.com: On 07/21/2015 08:34 AM, William Dunn wrote: Hello Aviel, On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila avie...@gmail.com mailto:avie...@gmail.com wrote: How can I set a highly available postgresql in a share-nothing architecture? I suggest you review the official documentation on high-availability configurations linked below: http://www.postgresql.org/docs/current/static/high-availability.html The most common configuration is to use PostgreSQL's built in master/standby streaming replication. However you will need to develop any fail-over logic you need yourself or use a third party tool such as EnterpriseDB's Failover Manager. Or use already available open source tools such as Pgpool-II or Linux-HA. Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you.
Re: [GENERAL] Cannot reach pg server using -h localhost
So that's all good. If I use -h it doesn't work: psql -Umyuser -d mydb -h localhost listen_addresses = 'localhost' # also '*', '127.0.0.1', '::1' Use netstat to look what IP postgres actually binds to. OS X uses the BSD syntax: netstat -an For example on my Mac (not homebrew): tcp4 0 0 127.0.0.1.5432 *.* LISTEN tcp6 0 0 ::1.5432 *.* LISTEN Bye, Chris. -- 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] Cannot reach pg server using -h localhost
Hosts file: 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost On Tue, Jul 21, 2015 at 7:42 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/21/2015 10:40 AM, Fritz Meissner wrote: CCing list On Tue, Jul 21, 2015 at 7:30 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: So what happens if you do?: psql -U myuser -d mydb -h 127.0.0.1 Same thing. Pause for about 30 seconds and then server closed the connection unexpectedly. Server log just shows: [unknown] LOG: connection received: host=127.0.0.1 port=51919 So what is in your hosts file? -- 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] Cannot reach pg server using -h localhost
Fritz Meissner fritz.meiss...@gmail.com writes: From the terminal in Mac OS X Yosemite, connecting to homebrew installed 9.4.4, I have a problem if I specify the host (I know this comes up all the time, bear with me as I have done a fair amount of digging already). This works: This rang a bell with me, and a bit of digging found this thread: http://www.postgresql.org/message-id/flat/6804.1413911...@sss.pgh.pa.us While that's not the same symptom you're describing, it does point up that OS X will expand localhost as pretty much whatever it says in /etc/hosts. You might want to make sure that that expansion is sane, ie 127.0.0.1 and ::1 and nothing else. On my not-misbehaving-now Yosemite laptop, /etc/hosts contains ## # Host Database # # localhost is used to configure the loopback interface # when the system is booting. Do not change this entry. ## 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost However, assuming that that's not your problem, I'd suggest cranking up log_min_messages to DEBUG5 and seeing if anything more gets reported in the postmaster log. Another thing worth checking is whether -h 127.0.0.1 and -h ::1 behave the same as -h localhost. That would help rule out (or not) DNS issues. regards, tom lane -- 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] Setting up HA postgresql
Hi, I made the following document 4 years back: http://www.rummandba.com/2011/02/postgresql-failover-with-pgpool-ii.html You may have a look if it makes any good to your work. BTW, if you want to setup a share-nothing high scalable system with data-sharding, you can go for pl/proxy. Thanks. On Tue, Jul 21, 2015 at 10:55 AM, Aviel Buskila avie...@gmail.com wrote: Can you link me up to a good tutorial using pgpool-II? 2015-07-21 20:02 GMT+03:00 Joshua D. Drake j...@commandprompt.com: On 07/21/2015 08:34 AM, William Dunn wrote: Hello Aviel, On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila avie...@gmail.com mailto:avie...@gmail.com wrote: How can I set a highly available postgresql in a share-nothing architecture? I suggest you review the official documentation on high-availability configurations linked below: http://www.postgresql.org/docs/current/static/high-availability.html The most common configuration is to use PostgreSQL's built in master/standby streaming replication. However you will need to develop any fail-over logic you need yourself or use a third party tool such as EnterpriseDB's Failover Manager. Or use already available open source tools such as Pgpool-II or Linux-HA. Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you.
Re: [GENERAL] Cannot reach pg server using -h localhost
and if you have it listen to 127.0.0.1, you see 127.0.0.1 in netstat? Yes, netstat reflects exactly what I have in listen_addresses: listen_addresses = '127.0.0.1, ::1' ~ netstat -an | grep 5432 tcp6 0 0 ::1.5432*.*LISTEN tcp4 0 0 127.0.0.1.5432 *.*LISTEN If it's just '127.0.0.1' it just lists the tcp4 line. and psql -h 127.0.0.1 still fails? Yes. is lo0 still there (ifconfig)? ~ ifconfig lo0 lo0: flags=8049UP,LOOPBACK,RUNNING,MULTICAST mtu 16384 options=3RXCSUM,TXCSUM inet6 ::1 prefixlen 128 inet 127.0.0.1 netmask 0xff00 inet6 fe80::1%lo0 prefixlen 64 scopeid 0x1 nd6 options=1PERFORMNUD -- 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] Setting up HA postgresql
On 07/21/2015 07:07 PM, Tatsuo Ishii wrote: But it appears that the fail condition for watchdog is the failure of a pgpool-II instance. In the configuration described in the wiki you would put a pgpool-II instance on each Postgres node, and if one of the pgpool-II instances fails it executes a script (which can create the trigger file to promote the standby to master). However, if the fail condition for watchdog is a failure of the pgpool-II instance what happens if the DBMS has availability issues but the pgpool-II process on that server is ok? The fail condition would never be triggered, right? No. The condition (PostgreSQL failure) will be detected by one of pgpool-II (not watchdog process) and the pgpool-II will disconnect the failed PostgreSQL, then execute failover script (if defined) which will usually promote standby PostgreSQL (if failed PostgreSQL was primary) or just ignore the broken PostgreSQL if it was standby. Thank you Tatsuo, I was having trouble explaining. Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] Setting up HA postgresql
But it appears that the fail condition for watchdog is the failure of a pgpool-II instance. In the configuration described in the wiki you would put a pgpool-II instance on each Postgres node, and if one of the pgpool-II instances fails it executes a script (which can create the trigger file to promote the standby to master). However, if the fail condition for watchdog is a failure of the pgpool-II instance what happens if the DBMS has availability issues but the pgpool-II process on that server is ok? The fail condition would never be triggered, right? No. The condition (PostgreSQL failure) will be detected by one of pgpool-II (not watchdog process) and the pgpool-II will disconnect the failed PostgreSQL, then execute failover script (if defined) which will usually promote standby PostgreSQL (if failed PostgreSQL was primary) or just ignore the broken PostgreSQL if it was standby. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 4:27 PM, Joshua D. Drake j...@commandprompt.com wrote: On 07/21/2015 01:21 PM, William Dunn wrote: That's pretty cool! But the intended use of watchdog is so you can have multiple pgpool-II instances and failover among them (http://www.pgpool.net/docs/latest/pgpool-en.html#watchdog) rather than failure of Postgres. In the configuration described in that wiki what happens when the DBMS goes down but pgpool-II is fine? The watchdog appears to be monitoring the pgpool-II process, not the postgres/postmaster process. From what I see, it has the ability to execute a command/script based on a failed condition which could include but not be limited to a command to fail over a standby. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] Setting up HA postgresql
On 07/21/2015 11:04 AM, William Dunn wrote: If you dig deeper into pgpool-II you will find that it does not have failover logic. Its intention is to pool connections and distribute query load among replicas, but it cannot differentiate node failure from network partition and cannot promote a standby to master in the case of failure. If you dig deeper, I think you would find your statement is no longer accurate: http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] Promoting 1 of 2 slaves
What I ended up doing was: 1. Stopping the master. 2. Triggering the promotion of slave A. 3. After slave A was promoted I turned it off and modified the postgresql.conf file to make it a permanent master and setup WAL shipping to slave B. 4. Rsync the pg_xlog directory from slave A to slave B and set recovery_target_timeline to latest 5. Start slave B, then start slave A now master. So far everything seems to work. -Joseph Kregloh On Tue, Jul 21, 2015 at 2:37 PM, William Dunn dunn...@gmail.com wrote: As I am aware, you would have two options depending on your configuration: 1. Change the primary_conninfo value on the second standby's recovery.conf to point to the standby that has been promoted to master. However, I think this would require that instance to be rebooted for the configuration changes to take effect 2. Modify the DNS entries so that the host value in primary_conninfo points to the new master rather than the old master Does anyone know of another way? *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Fri, Jul 17, 2015 at 9:43 AM, Joseph Kregloh jkreg...@sproutloud.com wrote: Hello, I am getting ready to take my master server out of service today and promote the slave to take it's place. In my current setup I have one master server which ships WAL files to two separate slave servers. My plan is to promote one of those slaves. How do I get the slave that I promoted to start replicating to the other slave the moment I trigger it's promotion? Thanks, -Joseph Kregloh
Re: [GENERAL] Cannot reach pg server using -h localhost
On 07/21/2015 11:00 AM, Fritz Meissner wrote: and if you have it listen to 127.0.0.1, you see 127.0.0.1 in netstat? Yes, netstat reflects exactly what I have in listen_addresses: listen_addresses = '127.0.0.1, ::1' ~ netstat -an | grep 5432 tcp6 0 0 ::1.5432*.*LISTEN tcp4 0 0 127.0.0.1.5432 *.*LISTEN If it's just '127.0.0.1' it just lists the tcp4 line. and psql -h 127.0.0.1 still fails? Yes. is lo0 still there (ifconfig)? ~ ifconfig lo0 lo0: flags=8049UP,LOOPBACK,RUNNING,MULTICAST mtu 16384 options=3RXCSUM,TXCSUM inet6 ::1 prefixlen 128 inet 127.0.0.1 netmask 0xff00 inet6 fe80::1%lo0 prefixlen 64 scopeid 0x1 nd6 options=1PERFORMNUD So what happens if you?: ping 127.0.0.1 ping localhost -- 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] Cannot reach pg server using -h localhost
So what happens if you?: ping 127.0.0.1 ping 127.0.0.1 PING 127.0.0.1 (127.0.0.1): 56 data bytes 64 bytes from 127.0.0.1: icmp_seq=0 ttl=64 time=0.044 ms 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.162 ms ... ping localhost ping localhost PING localhost (127.0.0.1): 56 data bytes 64 bytes from 127.0.0.1: icmp_seq=0 ttl=64 time=0.038 ms 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.128 ms ... -- 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] Postgres Recovery
archive_command = 'test ! -f /mnt/server/archivedir/%f cp %p /mnt/server/archivedir/%f' # Unix restore_command = 'cp /mnt/server/archivedir/%f %p' above two commands and checkpoint settings .as i read doc we need to change postgres.conf.But other way i want try it from server commmand mean from putty is it possible..? i want commands to execute the set to the server without open the postgres.conf...? On Mon, Jul 20, 2015 at 6:23 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Mon, Jul 20, 2015 at 7:00 PM, Ramesh T rameshparnandit...@gmail.com wrote: Hi All, What i need to know for postgres recovery..?let me know in detail. Documentation is always a good start: http://www.postgresql.org/docs/devel/static/backup.html -- Michael
Re: [GENERAL] Promoting 1 of 2 slaves
As I am aware, you would have two options depending on your configuration: 1. Change the primary_conninfo value on the second standby's recovery.conf to point to the standby that has been promoted to master. However, I think this would require that instance to be rebooted for the configuration changes to take effect 2. Modify the DNS entries so that the host value in primary_conninfo points to the new master rather than the old master Does anyone know of another way? *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Fri, Jul 17, 2015 at 9:43 AM, Joseph Kregloh jkreg...@sproutloud.com wrote: Hello, I am getting ready to take my master server out of service today and promote the slave to take it's place. In my current setup I have one master server which ships WAL files to two separate slave servers. My plan is to promote one of those slaves. How do I get the slave that I promoted to start replicating to the other slave the moment I trigger it's promotion? Thanks, -Joseph Kregloh
Re: [GENERAL] Setting up HA postgresql
On Tue, Jul 21, 2015 at 5:12 PM, Joshua D. Drake j...@commandprompt.com wrote: When backend node status changes by failover etc., watchdog notifies the information to other pgpool-IIs and synchronizes them. When online recovery occurs, watchdog restricts client connections to other pgpool-IIs for avoiding inconsistency between backends. You're taking that out of context, though. That is coming from the doc http://www.pgpool.net/docs/latest/pgpool-en.html#watchdog, which is describing how Watchdog coordinates fault of *pgpool-II* backend processes and coordinates the pgpool-II instances with each other. It is for maintaining availability of pgpool-II and monitoring for failure of pgpool-II backends, not Postgres/postmaster. *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 5:12 PM, Joshua D. Drake j...@commandprompt.com wrote: On 07/21/2015 01:37 PM, William Dunn wrote: But it appears that the fail condition for watchdog is the failure of a pgpool-II instance. In the configuration described in the wiki you would put a pgpool-II instance on each Postgres node, and if one of the pgpool-II instances fails it executes a script (which can create the trigger file to promote the standby to master). However, if the fail condition for watchdog is a failure of the pgpool-II instance what happens if the DBMS has availability issues but the pgpool-II process on that server is ok? The fail condition would never be triggered, right? What I am going off of is: When backend node status changes by failover etc., watchdog notifies the information to other pgpool-IIs and synchronizes them. When online recovery occurs, watchdog restricts client connections to other pgpool-IIs for avoiding inconsistency between backends. A backend node status would change if say the master went down, at that point pgpool can be instructed either automatically or manually to failover (depending on configuration) and allow the slave to failover. Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you.
Re: [GENERAL] Creating a user for pg_start_backup
On 7/21/2015 8:36 AM, Andrew Beverley wrote:
Sorry to be dragging this off-topic, but what's the reason for using su instead
of
sudo?
sudo is for non root users, it ends up running the su command. normally
root doesn't use sudo at all, look at all the init.d scripts that run
daemons as other users, they typically use su, like...
$SU -l postgres -c $PGENGINE/postmaster -p '$PGPORT' -D
'$PGDATA' ${PGOPTS} $PGLOG 21
/dev/null
(where $SU is su or runuser)
--
john r pierce, recycling bits in santa cruz
--
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] Setting up HA postgresql
Maybe Linux-HA which you recommended is the more promising option for open source tool. http://www.linux-ha.org/wiki/Main_Page The Postgres resource agent appears to monitor the instance by executing 'SELECT now();' which is typically the recommended check. Though, I do not know Linux-HA well enough to know of any limitations or whether it should be recommend http://linux-ha.org/doc/man-pages/re-ra-pgsql.html *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 5:35 PM, William Dunn dunn...@gmail.com wrote: On Tue, Jul 21, 2015 at 5:12 PM, Joshua D. Drake j...@commandprompt.com wrote: When backend node status changes by failover etc., watchdog notifies the information to other pgpool-IIs and synchronizes them. When online recovery occurs, watchdog restricts client connections to other pgpool-IIs for avoiding inconsistency between backends. You're taking that out of context, though. That is coming from the doc http://www.pgpool.net/docs/latest/pgpool-en.html#watchdog, which is describing how Watchdog coordinates fault of *pgpool-II* backend processes and coordinates the pgpool-II instances with each other. It is for maintaining availability of pgpool-II and monitoring for failure of pgpool-II backends, not Postgres/postmaster. *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 5:12 PM, Joshua D. Drake j...@commandprompt.com wrote: On 07/21/2015 01:37 PM, William Dunn wrote: But it appears that the fail condition for watchdog is the failure of a pgpool-II instance. In the configuration described in the wiki you would put a pgpool-II instance on each Postgres node, and if one of the pgpool-II instances fails it executes a script (which can create the trigger file to promote the standby to master). However, if the fail condition for watchdog is a failure of the pgpool-II instance what happens if the DBMS has availability issues but the pgpool-II process on that server is ok? The fail condition would never be triggered, right? What I am going off of is: When backend node status changes by failover etc., watchdog notifies the information to other pgpool-IIs and synchronizes them. When online recovery occurs, watchdog restricts client connections to other pgpool-IIs for avoiding inconsistency between backends. A backend node status would change if say the master went down, at that point pgpool can be instructed either automatically or manually to failover (depending on configuration) and allow the slave to failover. Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you.
Re: [GENERAL] Setting up HA postgresql
On 07/21/2015 02:48 PM, William Dunn wrote: Maybe Linux-HA which you recommended is the more promising option for open source tool. http://www.linux-ha.org/wiki/Main_Page The Postgres resource agent appears to monitor the instance by executing 'SELECT now();' which is typically the recommended check. Though, I do not know Linux-HA well enough to know of any limitations or whether it should be recommend http://linux-ha.org/doc/man-pages/re-ra-pgsql.html CMD has deployed many HA clusters using Linux-HA. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] Cannot reach pg server using -h localhost
This is interesting, I tried nc a few times. Inconsistent results: - most of the time the first line doesn't get through, even after a long wait - on sending a second line from the client both lines appear instantly, and it's 100% from there on - or I can send a line from the server. The first line from client is still lost but both ends are 100% after that - 1/10 sessions behave perfectly BTW ^D just ends the session without sending anything, I'm hitting enter to send instead. On Tue, Jul 21, 2015 at 11:05 PM, Chris Mair ch...@1006.org wrote: Hi, just to be 100% sure everything works upt to the TCP layer... 0. Stop postgres. 1. Open a terminal, enter: nc -l 5432 and leave that running. 2. Open another terminal and enter: nc 127.0.0.1 5432 follow up with some text such as hello and then hit CTRL-D So... did hello show up in the first Terminal or not? (it should) Bye, Chris. -- 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] Cannot reach pg server using -h localhost
On 07/21/2015 01:58 PM, Fritz Meissner wrote: 2) I know in your original post you did a which on psql, but is there more then one instance/install of Postgres on this machine? The reason I ask is this from the original post: psql -Umyuser -d mydb sql (9.3.5) Type help for help. myuser=# Yeah sorry, that's a copy/paste from before I upgraded out of desperation. Same thing was happening on 9.3.5 and 9.4.4. Currently: psql (9.4.4) Type help for help. myuser=# Well that would have been too easy. Have you tried Tom's suggestion?: However, assuming that that's not your problem, I'd suggest cranking up log_min_messages to DEBUG5 and seeing if anything more gets reported in the postmaster log. -- 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] Setting up HA postgresql
On 07/21/2015 01:21 PM, William Dunn wrote: That's pretty cool! But the intended use of watchdog is so you can have multiple pgpool-II instances and failover among them (http://www.pgpool.net/docs/latest/pgpool-en.html#watchdog) rather than failure of Postgres. In the configuration described in that wiki what happens when the DBMS goes down but pgpool-II is fine? The watchdog appears to be monitoring the pgpool-II process, not the postgres/postmaster process. From what I see, it has the ability to execute a command/script based on a failed condition which could include but not be limited to a command to fail over a standby. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] Cannot reach pg server using -h localhost
Hi, just to be 100% sure everything works upt to the TCP layer... 0. Stop postgres. 1. Open a terminal, enter: nc -l 5432 and leave that running. 2. Open another terminal and enter: nc 127.0.0.1 5432 follow up with some text such as hello and then hit CTRL-D So... did hello show up in the first Terminal or not? (it should) Bye, Chris. -- 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] Cannot reach pg server using -h localhost
On 07/21/2015 12:57 PM, Fritz Meissner wrote: So what happens if you?: ping 127.0.0.1 ping 127.0.0.1 PING 127.0.0.1 (127.0.0.1): 56 data bytes 64 bytes from 127.0.0.1: icmp_seq=0 ttl=64 time=0.044 ms 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.162 ms ... ping localhost ping localhost PING localhost (127.0.0.1): 56 data bytes 64 bytes from 127.0.0.1: icmp_seq=0 ttl=64 time=0.038 ms 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.128 ms ... Just realized that my suggestion above was redundant, because of this: psql -Umyuser -d mydb -h localhost psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Server log: [unknown] LOG: connection received: host=::1 port=51186 (... and that's it, no connection authorized line) So what happens if you do?: psql -U myuser -d mydb -h 127.0.0.1 Same thing. Pause for about 30 seconds and then server closed the connection unexpectedly. Server log just shows: [unknown] LOG: connection received: host=127.0.0.1 port=51919 So psql -h whatever is reaching the server, it is failing at that point though. Scratches head 1) Can you access the Postgres server from another machine? 2) I know in your original post you did a which on psql, but is there more then one instance/install of Postgres on this machine? The reason I ask is this from the original post: psql -Umyuser -d mydb sql (9.3.5) Type help for help. myuser=# That indicates a 9.3.5 version of psql. Not sure if that is the current state or a cut and paste from when you said you where running 9.3.5, before installing 9.4? -- 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] Setting up HA postgresql
That's pretty cool! But the intended use of watchdog is so you can have multiple pgpool-II instances and failover among them ( http://www.pgpool.net/docs/latest/pgpool-en.html#watchdog) rather than failure of Postgres. In the configuration described in that wiki what happens when the DBMS goes down but pgpool-II is fine? The watchdog appears to be monitoring the pgpool-II process, not the postgres/postmaster process. *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 3:31 PM, Joshua D. Drake j...@commandprompt.com wrote: On 07/21/2015 11:04 AM, William Dunn wrote: If you dig deeper into pgpool-II you will find that it does not have failover logic. Its intention is to pool connections and distribute query load among replicas, but it cannot differentiate node failure from network partition and cannot promote a standby to master in the case of failure. If you dig deeper, I think you would find your statement is no longer accurate: http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you.
Re: [GENERAL] Setting up HA postgresql
But it appears that the fail condition for watchdog is the failure of a pgpool-II instance. In the configuration described in the wiki you would put a pgpool-II instance on each Postgres node, and if one of the pgpool-II instances fails it executes a script (which can create the trigger file to promote the standby to master). However, if the fail condition for watchdog is a failure of the pgpool-II instance what happens if the DBMS has availability issues but the pgpool-II process on that server is ok? The fail condition would never be triggered, right? *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jul 21, 2015 at 4:27 PM, Joshua D. Drake j...@commandprompt.com wrote: On 07/21/2015 01:21 PM, William Dunn wrote: That's pretty cool! But the intended use of watchdog is so you can have multiple pgpool-II instances and failover among them (http://www.pgpool.net/docs/latest/pgpool-en.html#watchdog) rather than failure of Postgres. In the configuration described in that wiki what happens when the DBMS goes down but pgpool-II is fine? The watchdog appears to be monitoring the pgpool-II process, not the postgres/postmaster process. From what I see, it has the ability to execute a command/script based on a failed condition which could include but not be limited to a command to fail over a standby. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you.
Re: [GENERAL] Cannot reach pg server using -h localhost
2) I know in your original post you did a which on psql, but is there more then one instance/install of Postgres on this machine? The reason I ask is this from the original post: psql -Umyuser -d mydb sql (9.3.5) Type help for help. myuser=# Yeah sorry, that's a copy/paste from before I upgraded out of desperation. Same thing was happening on 9.3.5 and 9.4.4. Currently: psql (9.4.4) Type help for help. myuser=# -- 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] Setting up HA postgresql
On 07/21/2015 01:37 PM, William Dunn wrote: But it appears that the fail condition for watchdog is the failure of a pgpool-II instance. In the configuration described in the wiki you would put a pgpool-II instance on each Postgres node, and if one of the pgpool-II instances fails it executes a script (which can create the trigger file to promote the standby to master). However, if the fail condition for watchdog is a failure of the pgpool-II instance what happens if the DBMS has availability issues but the pgpool-II process on that server is ok? The fail condition would never be triggered, right? What I am going off of is: When backend node status changes by failover etc., watchdog notifies the information to other pgpool-IIs and synchronizes them. When online recovery occurs, watchdog restricts client connections to other pgpool-IIs for avoiding inconsistency between backends. A backend node status would change if say the master went down, at that point pgpool can be instructed either automatically or manually to failover (depending on configuration) and allow the slave to failover. Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] INSERT ... ON CONFLICT DO UPDATE
Hi Rafal: On Mon, Jul 20, 2015 at 3:33 PM, Rafal Pietrak ra...@ztk-rp.eu wrote: 3. there are methods (like cryptographic random sequence), which guarantee no conflicts. So one should resort to that. Regarding the last point. Usually, I implement one-time used vouchers as rows in table like: CREATE TABLE (voucher int not null, consumed bool, expire timestamp not null default timestamp_pl_interval(now()::timestamp, '2 min'::interval),..., unique (voucher,consumed) ); with CONSUMED column NULLyfied when voucher is used. The entire row of consumed voucher is purged after clearence and verification, which happen significantly later. Random as a primary key is considered a bad practice by many people with much experience, nullyfing it too. Many people even frown on just changing the primary key ( and one of the reasons for using serial as keys in many situations is to have a guaranteed not null unchanging value ). Such short lived (when active) voucher is usually just 6-digit long, to help people enter it. Then, random and with a narrow value domain, they make, IMNSHO, a really bad choice for primery keys. I don't know much about cryptography, but would a generic encryption function (like that indicated by Daniel) have the same waking through the entire range-space behavior as the original when that range-space is externally (by my application) truncated to those 6 digits? If not, would it be as efficient in conflict avoidance as used with original 32-bit range-space? An encryption function never has collisions ( do not confuse with a hash ). If it had you would be unable to decrypt it. The problem is the value domain for you. i.e., for your example you could choose a bit stream cipher applied to a 20 bit value. This is a moderately complex prolem to find or build ( from the classic cryptographic primitives nearly every language includes ). This will map every different 20 bit input value to a different 20 bit output value, so your value domain will be 20 bit numbers, your inputs will be the 10^6 6 digit numbers and the outputs will be 10^6 DIFFERENT 20bit numbers, of wich you could expect about 4.8% of them ( 2^20-10^6)/10^6 to have 7 digits ( with a leading one in this case ). To solve that problem you could use 19 digit input/output numbers or try to fin a decimal cypher which uses exactly 10^6 input digits. If you use a 32 bit block cypher it will not have collisions, but if you TRUNCATE the 32 bit ~ 9.5 digits output to 6 digits, you are no longer encrypting. You may call it hashing or whatever, but that is NOTt encryption, you would have collisions. Then again. Is it really a good practice to rely on a programmer to peek proper/correct encryption helper instead of providing him/her with a database-integrated tool for a well defined and not so rare usage scenario as random default for UNIQUE/PK column? Many of us are too old to get caught by this. This question is like asking Is it good practice to hit a person with a 10 pound hammer in the head instead of giving a cookie?. There are other options. IMO NOT modifying a very complex chunk of code ( the server code doing the inserts and checking the collision cases and acting on them, plus the parser for insert queries plus ) and risking all the bugs it may introduce to help with inserting random pk is good practice. It doesn't matter if the requesting programmer peeks a bad encryption methods, keeps his old code for inserting random ids or introduces bugs in his program, the potential harm to the rest of the users is too great. So my conclusion from this thread is that as this usage scenario does not seem to be foreseen by current implementation of ON CONFLICT transaction, a workaround exists (like: cryptographic range-walker). Being it a workaround, I'd vote for some direct supported of that scenario in the future at database level. Bear in mind your problem is totally ill defined. I mean, you want to insert a random 6 digits ID, and want the database to keep trying until it finds an unique one. What should it do if there already are 10^6 records in the db? Stall until a free one is found? abort? This kind of uses is very narrow, and very difficult to get right , and normally confined to the application domain. Even if you choose a totally correct encryption function for collision avoidance, like identity, you are going to have problems in your scheme. You are not voting for anything, you need a feature proposal to vote upon. So far the only one I could extract from this thread is something which magically solves the current Rafal problem. I would vote against that. Francisco Olarte. -- 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] INSERT ... ON CONFLICT DO UPDATE
Franscisco, W dniu 21.07.2015 o 09:34, Francisco Olarte pisze: Hi Rafal: On Mon, Jul 20, 2015 at 3:33 PM, Rafal Pietrak ra...@ztk-rp.eu wrote: Regarding the last point. Usually, I implement one-time used vouchers as rows in table like: CREATE TABLE (voucher int not null, consumed bool, expire timestamp not null default timestamp_pl_interval(now()::timestamp, '2 min'::interval),..., unique (voucher,consumed) ); with CONSUMED column NULLyfied when voucher is used. The entire row of consumed voucher is purged after clearence and verification, which happen significantly later. Such short lived (when active) voucher is usually just 6-digit long, to help people enter it. In this case I think you are mixing vouchers with voucher-numbers. IMO you could get a better dessign by using an auxiliary table and not nullifying the number after been consumed. Having only 6 digits I Hmmm. I don't think so. 1. I'm not nullifying the number, just the CONSUMED flag. The row stays otherwise pretty much untouched untill clearing time, when it's removed from the table. 2. And I don't thing I mix vouchers with voucher-numbers since there is no distinction. Bringing some real live examples of vouchers to back that later statement, we have: 1) a 6-digit authorization code (a voucher) used by payment system to confirm payment authorization. 2) 4-8digit one-time PIN delivered by SMS used to open some accounts. 3) 6-digit SMS confirmation code used by internet banking. 4) 14-digit voucher used to topup mobile pre-paied accounts. 5) 4-8 digit vouchers used as lunch tickets at conferences. (this could possibly used as printed qr-code of UUID, since cafeterias usually have bar-code readers; but having it as human-size 6-digit pin has it's benefits too). In all those cases the physical problem needs just a single N-digit number (a voucher), which is as short as it's lifespan/population allows for while keeping it relatively safe. The application just needs to create a unique (for a period of time) number, and consume it at certain point. Everything else would be implementation burden, which should be kept to minimum. tould try: 1.- Add a serial PK column to voucher table if needed to link it with the rest of the system. 2.- Create an index on voucher where consumed is true. 3.- Add another table, voucher_nums, with columns voucher, order, used. Populate it with the 10^6 vouchers and a random order value. Also, this lets you switch to alphanumeric vouchers, or zap the ones with two consecutive equal digits, or whatever. 4.- Make a function to select a free voucher, you can do 'select from voucher_nums where not used order by order limit 1¡', if yout put this into a with clause of an update-returning setting used to true to you get a one shot way of getting a free voucher. If you add a partial index on order where not used, you get a fast way of getting it. 5.- Make another function to free a voucher num, which sets consumed to true on vouchers, used to false and order to a random number on voucher_nums. This looks a bit like an overkill for the above examples. But I have other thoughts on the use of cryptographic sequences here. It has the pitfall of being sensitive to out-of-the-sequence poisoning, I mean: When another instance of an application starts issuing another sequence of vouchers, at certain point those sequences collide and applications despite using guaranteed lack of collisions will have a collision. So the application *will have to have* a re-issuing of an INSERT implemented anyway. If so, the whole point of using cryptographic sequence is missing. So, even though this collision is not statistically significant, but just its possibility results in that application have to take care of re-issuing of an INSERT. Using database.sequence() function to seed the cypher is not secure enough. On the other hand, the ON CONFLICT RETRY has a nice feature for an application programmer (like myself) that it leaves us free of the implementation of the re-issue of an INSERT. One database-schema designer does that for all of us. But knowing if that usage scenario is too rare to match the heavy lifting the implementation required, is beyond my experience. -R -- 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] Creating a user for pg_start_backup
On 7/21/2015 1:51 AM, Andrew Beverley wrote: Thanks John. The backup script is running as root, so presumably I'd have to use sudo? Or should I run a separate cron job as postgres to do the above, and run the backup script separately? those are both possibilities. I'd either use su (not sudo) from root, or I'd cron it from the postgres DBA account, depending. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Creating a user for pg_start_backup
Dear all, I'm setting up hot backups on my database server. As such, I'd like to set up a Postgres user that has access to only pg_start_backup and pg_stop_backup. I'm unable to work out how to do this with the various GRANT options. Can someone point me in the right direction please? Or is there a better way to achieve this, rather than having a dedicated user? Thanks, Andy -- 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] INSERT ... ON CONFLICT DO UPDATE
On 21 July 2015 at 11:43, Rafal Pietrak ra...@ztk-rp.eu wrote: On the other hand, the ON CONFLICT RETRY has a nice feature for an application programmer (like myself) that it leaves us free of the implementation of the re-issue of an INSERT. One database-schema designer does that for all of us. But knowing if that usage scenario is too rare to match the heavy lifting the implementation required, is beyond my experience. The usage scenario *is* rare and I'm sure that doesn't help you but the point is that it's very very easy to write a function that does what you want. It's not easy at all to write a function that does UPSERT or DO NOTHING consistently and efficiently. The fact that you refuse to use the mechanism provided to you by the database developers doesn't invalidate the fact that that's the simplest and easiest way to achieve what you want. I'm sorry to be harsh (again) about this but adding extra complexity to the PG system to achieve something that is _easily_ achieved through the existing mechanisms isn't something that is likely to get widespread support. Geoff
Re: [GENERAL] Creating a user for pg_start_backup
On 7/21/2015 1:31 AM, Andrew Beverley wrote:
I had to specify a database name when connecting:
psql -U backup -c select pg_start_backup('Daily backup') -d postgres
psql defaults to the current user for both the database name and user
name. I probably would have run that psql command as the system
postgres user and not specified any -U or -d ...
--
john r pierce, recycling bits in santa cruz
--
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] Creating a user for pg_start_backup
On Tue, 2015-07-21 at 01:46 -0700, John R Pierce wrote:
On 7/21/2015 1:31 AM, Andrew Beverley wrote:
I had to specify a database name when connecting:
psql -U backup -c select pg_start_backup('Daily backup') -d postgres
psql defaults to the current user for both the database name and user
name. I probably would have run that psql command as the system
postgres user and not specified any -U or -d ...
Thanks John. The backup script is running as root, so presumably I'd have to use
sudo? Or should I run a separate cron job as postgres to do the above, and run
the
backup script separately?
Thanks,
Andy
--
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] INSERT ... ON CONFLICT DO UPDATE
Hi Igor:
On Mon, Jul 20, 2015 at 4:56 PM, Igor Neyman iney...@perceptron.com wrote:
Well, there is a caveat.
If I create table and couple indexes like this:
..
and populate them:
and then check the size of the indexes:
for select pg_relation_size('U1') I get 2834432
while select pg_relation_size('U2') returns 2285568.
So, index based on randomly populated column is bigger than the one based on
sequentially populated.
But, on the other hand, after:
reindex table test_index_size;
both indexes are of the same size: 2260992.
I would totally expect this. On reindex you get the values from a tree
walk, so both of them come in order, and being a reindex ( where you
know in advance the full set of values, so you can plan ahead where to
put the leaves, how many levels you need and how many splits ) you get
an even bigger advantage from the squential insertion case.
Francisco Olarte.
--
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] INSERT ... ON CONFLICT DO UPDATE
Hi Alvaro. On Mon, Jul 20, 2015 at 4:07 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Some web research suggests that random sequences are not great for indexes because of the resultant keyspace fragmentation. I'm assuming that means a low number of nodes in the btree leafs, so an increase in memory usage for the index? Not sure what type of indexes would be affected by that problem, but I don't think Postgres' btrees would be. I do not know if postgres btrees do it, but I know you can build btree inserting code in such a way that inserting nodes sequentially leads to optimally filled leaf pages an denser trees, as an optimization for an easy and common case, which are better than the normal ones generated by random insertion. So is not that random are bad, it is that ordered are very good, or in another way thay are not affected by a problem, but do not get the advantage. Francisco Olarte. -- 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] INSERT ... ON CONFLICT DO UPDATE
On 7/20/2015 7:01 AM, Geoff Winkless wrote: Some web research suggests that random sequences are not great for indexes because of the resultant keyspace fragmentation. I'm assuming that means a low number of nodes in the btree leafs, so an increase in memory usage for the index? that suggests some folks overthink their indexing strategies and end up 'overoptimized'. anyways, a simple REINDEX fixes all sorts of index fragmentation -- john r pierce, recycling bits in santa cruz
Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE
Hi Rafal: On Mon, Jul 20, 2015 at 3:33 PM, Rafal Pietrak ra...@ztk-rp.eu wrote: Regarding the last point. Usually, I implement one-time used vouchers as rows in table like: CREATE TABLE (voucher int not null, consumed bool, expire timestamp not null default timestamp_pl_interval(now()::timestamp, '2 min'::interval),..., unique (voucher,consumed) ); with CONSUMED column NULLyfied when voucher is used. The entire row of consumed voucher is purged after clearence and verification, which happen significantly later. Such short lived (when active) voucher is usually just 6-digit long, to help people enter it. In this case I think you are mixing vouchers with voucher-numbers. IMO you could get a better dessign by using an auxiliary table and not nullifying the number after been consumed. Having only 6 digits I tould try: 1.- Add a serial PK column to voucher table if needed to link it with the rest of the system. 2.- Create an index on voucher where consumed is true. 3.- Add another table, voucher_nums, with columns voucher, order, used. Populate it with the 10^6 vouchers and a random order value. Also, this lets you switch to alphanumeric vouchers, or zap the ones with two consecutive equal digits, or whatever. 4.- Make a function to select a free voucher, you can do 'select from voucher_nums where not used order by order limit 1¡', if yout put this into a with clause of an update-returning setting used to true to you get a one shot way of getting a free voucher. If you add a partial index on order where not used, you get a fast way of getting it. 5.- Make another function to free a voucher num, which sets consumed to true on vouchers, used to false and order to a random number on voucher_nums. This way you keep the old voucher numbers, and you get no collisions. If you run for some years, you can see which vouchers have been used, so you can debug potential problems. Francisco Olarte. -- 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] Creating a user for pg_start_backup
Andrew Beverley wrote: I'm setting up hot backups on my database server. As such, I'd like to set up a Postgres user that has access to only pg_start_backup and pg_stop_backup. I'm unable to work out how to do this with the various GRANT options. Can someone point me in the right direction please? Or is there a better way to achieve this, rather than having a dedicated user? If you want to be as restrictive as possible, you could create functions owned by a superuser with SECURITY DEFINER that do only these things and give execution rights only to a user that has no other privileges. Youes, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
