Re: [GENERAL] RHEL 7
2016-07-08 20:58 GMT+02:00 John R Pierce : > On 7/8/2016 11:52 AM, Paul Tilles - NOAA Federal wrote: > >> I am currently running Version 9.3.10 of postgres with RHEL 6. I am >> going to upgrade my O/S soon to RHEL 7. Do I need to upgrade to version >> 9.4.x of postgres? >> > > was 9.3 installed from the PGDG yum repository, or from the default RHEL > repository? > > frankly, I would NOT do an in place upgrade of RHEL6 to RHEL7, True, somewhere in RHEL 7 online guides it's stated that it's not recommended to use the migration procedure (so I wonder why they've included it). -- *Christian Castelliskype: christrack*
Re: [GENERAL] Streaming replication failover process - Pgsql 9.2
On 07/10/2016 07:17 PM, Patrick B wrote: If the master server can't send the wal_files through the slaves, shouldn't the wal_files be in "background" waiting to be delivered? Short version, yes, assuming you are talking about archiving the WAL files somewhere and assuming there is sufficient space for the quantity of WAL files stored on what ever 'device' you are storing them on. Long version and, I would say, required reading: https://www.postgresql.org/docs/9.2/static/high-availability.html Otherwise what's the purpose of them? If a network fails I'd loose those files? -- 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] Streaming replication failover process - Pgsql 9.2
On 7/10/2016 8:51 PM, Patrick B wrote: what if the network goes down? that WAL server could be located in the same data center as the master database server. if your local area network goes down, well, you're probably in a world of hurt. if the wide area network is mission critical, it would be smart to have multiple resilient network paths -- 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] Streaming replication failover process - Pgsql 9.2
2016-07-11 15:48 GMT+12:00 John R Pierce : > On 7/10/2016 4:28 PM, Patrick B wrote: > >> >> archive_command = 'cp %p /var/lib/pgsql/archive/%f' >> >> >> That would be ok right guys? >> >> > > normally, you want to ship your WAL archives to a NFS server or something > similar, which the master and all the slaves can read. what if the network goes down?
Re: [GENERAL] Streaming replication failover process - Pgsql 9.2
On 7/10/2016 4:28 PM, Patrick B wrote: archive_command = 'cp %p /var/lib/pgsql/archive/%f' That would be ok right guys? normally, you want to ship your WAL archives to a NFS server or something similar, which the master and all the slaves can read. -- 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] Streaming replication failover process - Pgsql 9.2
oh ok.. got it.. wal_keep_segments = To prevent the primary server from removing the WAL segments required for the standby server before shipping them, set the minimum number of segments retained in the pg_xlog directory so it would be ok just by increasing that parameter, right? Once the servers are back online, the shipping should start normally. Is that right guys? cheers
Re: [GENERAL] Streaming replication failover process - Pgsql 9.2
If the master server can't send the wal_files through the slaves, shouldn't the wal_files be in "background" waiting to be delivered? Otherwise what's the purpose of them? If a network fails I'd loose those files?
Re: [GENERAL] [BUGS] Where clause in pg_dump: need help
On Sun, Jul 10, 2016 at 12:53 PM, Prashanth Adiyodi wrote: > 1.- You have a backup with a series of tables which get inserted WITH a > timestamp. > Adi-The series of tables may or may not have timestamp > Then I think you cannot do what you want using only built-in PostgreSQL capabilities. Meta data about when a record was inserted and/or delete is not kept by the system. I'm not familiar with the capabilities of logical replication so that may provide an answer I am unaware of. Otherwise the only thing that comes to mind is to stream WAL to an intermediate server and then every so often (once a day) release to the other server all WAL that accumulated during the previous period. None of \copy, COPY (SQL), or pg_dump will get you what you want - the persisted data doesn't contain the information you desired. Alternatively, once a week (give or take) you could perform a base backup of the DB. Stream all archive files somewhere and each day perform a "point in time" recovery. David J.
Re: [GENERAL] Streaming replication failover process - Pgsql 9.2
2016-07-11 12:18 GMT+12:00 Adrian Klaver : > On 07/10/2016 04:28 PM, Patrick B wrote: > >> archive_command = 'cp %p /var/lib/pgsql/archive/%f' >> > > This would be where? > master server > > And does the corresponding restore_command point to the same place? yes.. the slaves have the restore_command pointing to the same place. As they won't be able to communicate with master, once all the services are up again, will have to copy them manually > > > >> >> That would be ok right guys? >> >> I will also setup wal_keep_segments to 512 >> >> > The WAL segments kept would be dependent the number of WAL segments your > database generates on average over the worse case downtime interval plus a > CYA buffer. > >
Re: [GENERAL] Streaming replication failover process - Pgsql 9.2
On 07/10/2016 04:28 PM, Patrick B wrote: archive_command = 'cp %p /var/lib/pgsql/archive/%f' This would be where? And does the corresponding restore_command point to the same place? That would be ok right guys? I will also setup wal_keep_segments to 512 The WAL segments kept would be dependent the number of WAL segments your database generates on average over the worse case downtime interval plus a CYA buffer. -- 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] Streaming replication failover process - Pgsql 9.2
archive_command = 'cp %p /var/lib/pgsql/archive/%f' That would be ok right guys? I will also setup wal_keep_segments to 512
Re: [GENERAL] Streaming replication failover process - Pgsql 9.2
thanks guys.. thanks for all the comments... I'm not shipping the wal_files into master, I actually ship them into slave and another backup server as well. So I'll have to change my archive_command then :) Thanks!
Re: [GENERAL] Streaming replication failover process - Pgsql 9.2
On 7/10/2016 2:42 PM, Andreas Kretschmer wrote: 1. When the connection comes back, will the master and slave work as expected? The streaming replication should be ok? if the master holds all needed WAL's there should be no problem. You can ensure that with wal_keep_segments, or, in newer version, via replication slots. or via an external WAL archive, that the master writes, and the slave has access to. -- john r pierce, recycling bits in santa cruz
Re: [GENERAL] Streaming replication failover process - Pgsql 9.2
Am 10.07.2016 um 23:19 schrieb Patrick B: Hi all, There will be a network maintenance at the company where my servers are... I've got one master and one slave server, running PostgreSQL 9.2. As the network will be down, the internet won't be working as well as the intranet. Both servers won't be able to communicate each other. Not by streaming replication and wal_files too. That should take at most 10 minutes. 1. When the connection comes back, will the master and slave work as expected? The streaming replication should be ok? if the master holds all needed WAL's there should be no problem. You can ensure that with wal_keep_segments, or, in newer version, via replication slots. -- 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] Streaming replication failover process - Pgsql 9.2
On 07/10/2016 02:19 PM, Patrick B wrote: Hi all, There will be a network maintenance at the company where my servers are... I've got one master and one slave server, running PostgreSQL 9.2. As the network will be down, the internet won't be working as well as the intranet. Both servers won't be able to communicate each other. Not by streaming replication and wal_files too. That should take at most 10 minutes. 1. When the connection comes back, will the master and slave work as expected? The streaming replication should be ok? Assuming you have wal_keep_segments set high enough to keep the master from recycling the WAL files before the network comes up again. 2. As the master will be down, I don't wanna slave turns into a master by Failover. The trigger_file line on recovery.conf on the slave server is commented, so should be ok here right? The slave will be still a slave once master is down Thanks! Patrick -- 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] Streaming replication failover process - Pgsql 9.2
On 7/10/2016 2:19 PM, Patrick B wrote: 1. When the connection comes back, will the master and slave work as expected? The streaming replication should be ok? as long as you have sufficient WAL available it should recover fine. you might have to restart the slave to get it to reconnect. 2. As the master will be down, I don't wanna slave turns into a master by Failover. The trigger_file line on recovery.conf on the slave server is commented, so should be ok here right? The slave will be still a slave once master is down failover is a function of whatever cluster management software you use, postgres won't failover on its own. -- 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] Streaming replication failover process - Pgsql 9.2
Hi all, There will be a network maintenance at the company where my servers are... I've got one master and one slave server, running PostgreSQL 9.2. As the network will be down, the internet won't be working as well as the intranet. Both servers won't be able to communicate each other. Not by streaming replication and wal_files too. That should take at most 10 minutes. 1. When the connection comes back, will the master and slave work as expected? The streaming replication should be ok? 2. As the master will be down, I don't wanna slave turns into a master by Failover. The trigger_file line on recovery.conf on the slave server is commented, so should be ok here right? The slave will be still a slave once master is down Thanks! Patrick
Re: [GENERAL] [BUGS] Where clause in pg_dump: need help
Hi Franciso, My comments below inline
-Original Message-
From: Francisco Olarte [mailto:fola...@peoplecall.com]
Sent: Friday, July 08, 2016 3:07 PM
To: Prashanth Adiyodi
Cc: pgsql-general@postgresql.org
Subject: Re: [BUGS] Where clause in pg_dump: need help
1.- CCing to the list ( remember to hit reply-all or whatever your MUA uses for
that, otherwise threads may get lost ).
2.- Try to avoid top-posting, it's not the style of the list ( or mine ).
On Fri, Jul 8, 2016 at 4:43 AM, Prashanth Adiyodi
wrote:
> Basically my requirement is, I have a live Db with certain tables and
> a backup Db at another location (both on postgressql). I need to take
> a backup of this live DB every night for the previous day (i.e the
> backup script running on 07/07/2016 will take the backup of the DB for
> 06/07/2016). This backup will be then transferred to the backup DB
> server and will be inserted into that DB. From what I have read
> pg_dump is the solution (similar to export in oracle), do you think of
> any other approach to get to this objective, have you come across a
> script or something that already does this,
Your requirement is a bit 'understated'. I assume your problem is:
1.- You have a backup with a series of tables which get inserted WITH a
timestamp.
Adi-The series of tables may or may not have timestamp
2.- At the end of the day you want to transfer the inserted data, and only the
inserted data, to another server and insert it ther.
Adi-Exactly., somewhere post midnight I need to transfer the inserted data for
the day to another DB.
If BOTH servers are postgres, you can do it easily with a series of COPY
commands easily. If the target one is not postgres I would use it too, but pass
the COPY data through a perl script to generate whatever syntax the target DB
needs ( I've done that to go from postgres to sql server and back using
freebcp, IIRC, on the sql server side )
You still can have problems IF you have updates to the tables, or deletions, or
. But if you just have
insertions, copy is easy to do.
Adi-
I am OK with the copy command, however I am not able to understand (my bad, I
am not used to postgres and using for the 1st time) the where clause that
should be used to achieve this result.
I tries using something like the below,
psql -d my_db -c 'copy (select * from mytab WHERE date_trunc('day',NOW() -
interval '1 day') TO STDOUT' -o data1.copy;
but this, I am sure has some syntax errors, could you help correct this,
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] error when upgrading 9.4 to 9.5 manually
On 07/10/2016 03:13 AM, arnaud gaboury wrote: My box upgraded postgresql from 9.4.8 to 9.5.3 when doing a full version upgrade (Fedora 23 --> 24). I have now : /db/pgsal/data > initialized with 9.5, with postgresql.conf and pg_hab.conf from 9.4 /db/pgsql/data.old > the old one from 9.4 Which is the opposite of what you said here: https://www.postgresql.org/message-id/CAK1hC9t6h9CENXQ6FHchx1Ny1AAAuYf2ARTpeospz1Jd7q_qLg%40mail.gmail.com How can I start postgres with socket location in /db/pgsql ? There is no option in pg_ctl to indicate socket directory.? Thank you for help At this point I think your best option is to start over, if that is possible. By this I mean: 1) See if it is possible to get a Fedora 24 package from Fedora for Postgres 9.4. 2) If it is make sure you have the existing 9.4 cluster saved somewhere out of the way. 3) Uninstall Postgres 9.5 4) Install Postgres 9.4 and copy the cluster you saved 2) into the data directory. 5) Decide whether you want to stay with 9.4 or move up to 9.5. 6) If you do decide to move up take a pg_dump of the 9.4 data before you do anything. -- 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] error when upgrading 9.4 to 9.5 manually
arnaud gaboury writes: > Ok. With the data from my 9-4, i.e /db/pgsql/data, and with > postgresql.service stopped. > # postgresql-setup --upgrade > * Upgrading database. > ERROR: pg_upgrade tool failed > ERROR: Upgrade failed. > * See /var/lib/pgsql/upgrade_postgresql.log for details. > # cat /var/lib/pgsql/upgrade_postgresql.log > *failure* > Consult the last few lines of "pg_upgrade_server.log" for > the probable cause of the failure. > connection to database failed: could not connect to server: No such > file or directory > Is the server running locally and accepting > connections on Unix domain socket "/home/postgres/.s.PGSQL.5432"? > could not connect to old postmaster started with the command: > "/usr/lib64/pgsql/postgresql-9.4/bin/pg_ctl" -w -l > "pg_upgrade_server.log" -D "/db/pgsql/data-old" -o "-p 5432 -b -c > listen_addresses='' -c unix_socket_permissions=0700 -c > unix_socket_directories='/home/postgres'" start > As you can see, postgres can't find the socket You're jumping to a conclusion not supported by this evidence; we can't tell whether the postmaster started at all. Did you look into pg_upgrade_server.log as suggested? 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] error when upgrading 9.4 to 9.5 manually
On Sun, Jul 10, 2016 at 5:36 PM, Tom Lane wrote: > arnaud gaboury writes: >> My box upgraded postgresql from 9.4.8 to 9.5.3 when doing a full >> version upgrade (Fedora 23 --> 24). > > So I assume you're using the Fedora-supplied RPMs, not the PGDG ones? > > (I'm not real sure, but I think the upgrade process would not have > chosen to replace non-Fedora RPMs. If it did, that might be part of > your problem; I believe the Fedora packaging is still a bit different > directory-layout-wise from PGDG. You'd be better off sticking with the > same PG package source.) > >> Now I think I shall run this command with postgres off: >> $ pg_upgrade -b /usr/lib64/pgsql/postgresql-9.4/bin -B /usr/bin -d >> /db/pgsql/data.old -D /db/pgsql/data > > Why are you not using "postgresql-setup upgrade", as documented in > /usr/share/doc/postgresql-NNN/README.rpm-dist ? Ok. With the data from my 9-4, i.e /db/pgsql/data, and with postgresql.service stopped. # postgresql-setup --upgrade * Upgrading database. ERROR: pg_upgrade tool failed ERROR: Upgrade failed. * See /var/lib/pgsql/upgrade_postgresql.log for details. # cat /var/lib/pgsql/upgrade_postgresql.log *failure* Consult the last few lines of "pg_upgrade_server.log" for the probable cause of the failure. connection to database failed: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/home/postgres/.s.PGSQL.5432"? could not connect to old postmaster started with the command: "/usr/lib64/pgsql/postgresql-9.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/db/pgsql/data-old" -o "-p 5432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/home/postgres'" start As you can see, postgres can't find the socket > > regards, tom lane -- google.com/+arnaudgabourygabx -- 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] error when upgrading 9.4 to 9.5 manually
arnaud gaboury writes: > My box upgraded postgresql from 9.4.8 to 9.5.3 when doing a full > version upgrade (Fedora 23 --> 24). So I assume you're using the Fedora-supplied RPMs, not the PGDG ones? (I'm not real sure, but I think the upgrade process would not have chosen to replace non-Fedora RPMs. If it did, that might be part of your problem; I believe the Fedora packaging is still a bit different directory-layout-wise from PGDG. You'd be better off sticking with the same PG package source.) > Now I think I shall run this command with postgres off: > $ pg_upgrade -b /usr/lib64/pgsql/postgresql-9.4/bin -B /usr/bin -d > /db/pgsql/data.old -D /db/pgsql/data Why are you not using "postgresql-setup upgrade", as documented in /usr/share/doc/postgresql-NNN/README.rpm-dist ? 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
[GENERAL] error when upgrading 9.4 to 9.5 manually
My box upgraded postgresql from 9.4.8 to 9.5.3 when doing a full version upgrade (Fedora 23 --> 24). I have now : /db/pgsal/data > initialized with 9.5, with postgresql.conf and pg_hab.conf from 9.4 /db/pgsql/data.old > the old one from 9.4 /usr/lib64/pgsql/postgresql-9.4/bin which comes from postgresql-upgrade Fedora package. $ ls /usr/lib64/pgsql/postgresql-9.4/bin drwxr-xr-x 1 root root 22 Jul 7 09:21 ../ -rwxr-xr-x 1 root root 28K May 12 15:34 pg_controldata* -rwxr-xr-x 1 root root 41K May 12 15:34 pg_ctl* -rwxr-xr-x 1 root root 29K May 12 15:34 pg_isready* -rwxr-xr-x 1 root root 45K May 12 15:34 pg_receivexlog* -rwxr-xr-x 1 root root 49K May 12 15:34 pg_recvlogical* -rwxr-xr-x 1 root root 36K May 12 15:34 pg_resetxlog* -rwxr-xr-x 1 root root 5.8M May 12 15:34 postgres* lrwxrwxrwx 1 root root8 May 12 15:33 postmaster -> postgres* Now I think I shall run this command with postgres off : $ pg_upgrade -b /usr/lib64/pgsql/postgresql-9.4/bin -B /usr/bin -d /db/pgsql/data.old -D /db/pgsql/data But it reurns this error: connection to database failed: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/db/pgsql/.s.PGSQL.50432"? could not connect to old postmaster started with the command: "/usr/lib64/pgsql/postgresql-9.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/db/pgsql/data.old" -o "-p 50432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/db/pgsql'" start -- Posgresql is started with systemd service file and its socket is in /run/postgresql How can I start postgres with socket location in /db/pgsql ? There is no option in pg_ctl to indicate socket directory.? Thank you for help -- google.com/+arnaudgabourygabx -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
