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

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 f

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=# Y

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 lin

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

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 enou

Re: [GENERAL] Setting up HA postgresql

On Tue, Jul 21, 2015 at 5:12 PM, Joshua D. Drake 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

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

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

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

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 p

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

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

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 localhos

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 oth

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

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 f

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

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 sta

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 confi

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*.*

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 cre

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

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

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

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 : > > On 07/21/2015 08:34 AM, William Dunn wrote: > >> Hello Aviel, >> >> On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila > > wrote: >> >> How can I set a highly availabl

Re: [GENERAL] Cannot reach pg server using -h localhost

Fritz Meissner 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

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

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 wrote: > On 07/21/2015 10:40 AM, Fritz Meissner wrote: > > CCing list >> >> On Tue, Jul 21, 2015 at 7:30 PM, Adrian Klaver >> wrote: >> >>> So what happen

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 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

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 9767b1ca01

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): tc

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

[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. myuse

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 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 c

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 : > Hello Aviel, > > On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila wrote: >> >> How can I set a highly available postgresql in a share-nothing >> architecture? >>

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 > >

Re: [GENERAL] Setting up HA postgresql

Hello Aviel, On Tue, Jul 21, 2015 at 3:56 AM, Aviel Buskila 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/

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hi Rafal: On Tue, Jul 21, 2015 at 12:43 PM, Rafal Pietrak 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

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 d

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

On 21 July 2015 at 11:43, Rafal Pietrak 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

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 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 tim

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

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 datab

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

Re: [GENERAL] Creating a user for pg_start_backup

On Tue, 2015-07-21 at 16:54 +0900, Michael Paquier wrote: > On Tue, Jul 21, 2015 at 4:47 PM, Andrew Beverley wrote: > > 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_back

[GENERAL] Setting up HA postgresql

Hey, How can I set a highly available postgresql in a share-nothing architecture? Best regards, Aviel B.

Re: [GENERAL] Creating a user for pg_start_backup

On Tue, Jul 21, 2015 at 4:47 PM, Andrew Beverley wrote: > 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 op

[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 ther

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hi Rafal: On Mon, Jul 20, 2015 at 3:33 PM, Rafal Pietrak 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 > mi

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 su

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hi Rafal: On Mon, Jul 20, 2015 at 3:33 PM, Rafal Pietrak 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 TA

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hi Igor: On Mon, Jul 20, 2015 at 4:56 PM, Igor Neyman 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')"

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hi Alvaro. On Mon, Jul 20, 2015 at 4:07 PM, Alvaro Herrera 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