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
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:
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
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
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
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
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
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.
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
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
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
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
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
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
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
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
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.
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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=#
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
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
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
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
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
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
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
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:
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,
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
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
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
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
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
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
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
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
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
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
51 matches
Mail list logo
