subject:"\[GENERAL\] Upgrade"


On Tue, 15 Nov 2016, Adrian Klaver wrote:


So what do you get with
/usr/bin/pg_ctl --version


  Interesting:

$ /usr/bin/pg_ctl --version
pg_ctl (PostgreSQL) 9.6.1


and given John's suggestion:
ls -al /usr/bin/pg_ctl


ls -al /usr/bin/pg_ctl
lrwxrwxrwx 1 root root 32 Nov 15 14:16 /usr/bin/pg_ctl ->
../lib/postgresql/9.6/bin/pg_ctl*

   Aha! That's where the bin/ directories are. There's a 9.3.4/ and 9.5/
subdirectory there, too.

  Will do a pg_dumpall and then run pg_upgrade.

Thanks, both of you. I learned valuable lessons,

Rich


--
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] Upgrade from 9.5.4 to 9.6.1


On 11/15/2016 05:18 PM, Rich Shepard wrote:

On Tue, 15 Nov 2016, Adrian Klaver wrote:


Rich is using Slackware and I am pretty sure it marches to a different
drummer.


Adrian,

  And a different band. Pat likes MariaDB which replaced MySQL so
PostgreSQL
is not part of the base distribution. But, SlackBuilds.org provides a build
script that's worked for me since some early version 7 of postgres.


To add to my previous post. If you do decide to follow the pg_upgrade 
procedure in the README do a pg_dump of the 9.5 data just before you do 
pg_ugrade and store it away in a safe place. The first time through a 
new process does not always end well:)




Regards,

Rich





--
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] Upgrade from 9.5.4 to 9.6.1


On 11/15/2016 05:18 PM, Rich Shepard wrote:

On Tue, 15 Nov 2016, Adrian Klaver wrote:


Rich is using Slackware and I am pretty sure it marches to a different
drummer.


Adrian,

  And a different band. Pat likes MariaDB which replaced MySQL so
PostgreSQL
is not part of the base distribution. But, SlackBuilds.org provides a build
script that's worked for me since some early version 7 of postgres.


Aah, so the answer is here:

https://slackbuilds.org/slackbuilds/14.2/system/postgresql/README.SBo

"A few hints for PostgreSQL 9.5 -> 9.6 upgrade:

"



Regards,

Rich





--
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] Upgrade from 9.5.4 to 9.6.1


On 11/15/2016 05:06 PM, Rich Shepard wrote:

On Tue, 15 Nov 2016, Adrian Klaver wrote:


Assuming the bindirs are in your $PATH:

aklaver@panda:~> whereis -f pg_ctl


Adrian,

  In my case:

$ whereis -f pg_ctl
pg_ctl: /bin/pg_ctl /usr/bin/pg_ctl /usr/X11R6/bin/pg_ctl
/usr/bin/X11/pg_ctl /usr/X11/bin/pg_ctl /usr/man/man1/pg_ctl.1
/usr/man/man1/pg_ctl.1.gz /usr/share/man/man1/pg_ctl.1
/usr/share/man/man1/pg_ctl.1.gz /usr/X11/man/man1/pg_ctl.1
/usr/X11/man/man1/pg_ctl.1.gz


So what do you get with

/usr/bin/pg_ctl --version

and given John's suggestion:

ls -al /usr/bin/pg_ctl




Even if only one is the $PATH:



you can usually figure out where the other is.


  I suppose if I knew which version is in each of those directories it
would
make life easier.

  Currently, only 9.5.4 is running; 9.6.1 is installed and initiated but
not
running. When I invoke 'psql -l' I see the clusters installed in 9.5.4.


So how are you going to start the 9.6 instance?



  Will use pg_dumpall and pg_restore ... tomorrow morning.

Thanks very much,

Rich





--
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] Upgrade from 9.5.4 to 9.6.1


On Tue, 15 Nov 2016, Adrian Klaver wrote:

Rich is using Slackware and I am pretty sure it marches to a different 
drummer.


Adrian,

  And a different band. Pat likes MariaDB which replaced MySQL so PostgreSQL
is not part of the base distribution. But, SlackBuilds.org provides a build
script that's worked for me since some early version 7 of postgres.

Regards,

Rich


--
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] Upgrade from 9.5.4 to 9.6.1


On 11/15/2016 05:01 PM, John R Pierce wrote:

On 11/15/2016 4:55 PM, Adrian Klaver wrote:

where do I look for the two bindirs?


Assuming the bindirs are in your $PATH:

aklaver@panda:~> whereis -f pg_ctl
pg_ctl: /usr/local/pgsql/bin/pg_ctl /usr/local/pgsql94/bin/pg_ctl

Even if only one is the $PATH:

aklaver@panda:~> whereis -f pg_ctl
pg_ctl: /usr/local/pgsql/bin/pg_ctl

you can usually figure out where the other is.


the RHEL style distributions don't put the actual bin dirs in the path,
rather, they put symlinks to the common user utilities in /usr/bin
managed via a system known as 'alternates'.


Rich is using Slackware and I am pretty sure it marches to a different 
drummer.


http://www.slackware.com/faq/do_faq.php?faq=installation#2




--
john r pierce, recycling bits in santa cruz




--
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] Upgrade from 9.5.4 to 9.6.1


On Tue, 15 Nov 2016, Adrian Klaver wrote:


Assuming the bindirs are in your $PATH:

aklaver@panda:~> whereis -f pg_ctl


Adrian,

  In my case:

$ whereis -f pg_ctl
pg_ctl: /bin/pg_ctl /usr/bin/pg_ctl /usr/X11R6/bin/pg_ctl
/usr/bin/X11/pg_ctl /usr/X11/bin/pg_ctl /usr/man/man1/pg_ctl.1
/usr/man/man1/pg_ctl.1.gz /usr/share/man/man1/pg_ctl.1
/usr/share/man/man1/pg_ctl.1.gz /usr/X11/man/man1/pg_ctl.1
/usr/X11/man/man1/pg_ctl.1.gz


Even if only one is the $PATH:



you can usually figure out where the other is.


  I suppose if I knew which version is in each of those directories it would
make life easier.

  Currently, only 9.5.4 is running; 9.6.1 is installed and initiated but not
running. When I invoke 'psql -l' I see the clusters installed in 9.5.4.

  Will use pg_dumpall and pg_restore ... tomorrow morning.

Thanks very much,

Rich


--
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] Upgrade from 9.5.4 to 9.6.1

2016-11-15 Thread John R Pierce


On 11/15/2016 4:55 PM, Adrian Klaver wrote:

where do I look for the two bindirs?


Assuming the bindirs are in your $PATH:

aklaver@panda:~> whereis -f pg_ctl
pg_ctl: /usr/local/pgsql/bin/pg_ctl /usr/local/pgsql94/bin/pg_ctl

Even if only one is the $PATH:

aklaver@panda:~> whereis -f pg_ctl
pg_ctl: /usr/local/pgsql/bin/pg_ctl

you can usually figure out where the other is. 


the RHEL style distributions don't put the actual bin dirs in the path, 
rather, they put symlinks to the common user utilities in /usr/bin 
managed via a system known as 'alternates'.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1


On Tue, 15 Nov 2016, John R Pierce wrote:


9.5 is considered a major version, 9.5.4 is a minor.this will change
when 10 is released, from 10 on, major versions will be 10, 11, 12, ...


John,

  So the upgrade from 9.5.4 to 9.6.1 is as I assumed: major.


based on the data paths you gave, I'm guessing you're on a
redhat/centos/fedora type distribution? those put the binaries in
/usr/pgsql-X.Y/bin for version X.Y


  Actually, slackware. No /usr/pgsql-x/ at all. Slackware binaries (and
packages such as postgres provided by SlackBuilds.org are all installed in
/bin/. There are no /bin directories associated with postgres.

  So, I'll go the pg_dumpall route then.

Thanks very much,

Rich



--
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] Upgrade from 9.5.4 to 9.6.1


On 11/15/2016 02:37 PM, Rich Shepard wrote:

  All my previous version upgrades were performed by running pg_dumpall in
the older version followed by running 'pgsql -f ...' to install the dumped
.sql file, usually because the upgrade jumped several versions. Now I'd
like
to try the available postgres commands.

  The older version is installed in /var/lib/pgsql/9.5/data and I just
initiated the new version in /var/lib/pgsql/9.6/data. The earlier
version is
currently running.

  Is pg_upgrade the recommended way to upgrade from one minor version to
the
next? The 9.5 manual recommends this approach for _major_ upgrades (e.g.,
8.4.7 to 9.6.1), but not for _minor_ upgrades (e.g., 9.0.1 to 9.0.4).
That's
a first digit upgrade and a third digit upgrade. Since 9.5.4 to 9.6.1 is a
second digit upgrade I suppose it's semi-major, but in which upgrade camp
does it belong?

  The command is:

pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir

and I don't know where to find -b and -B. On my Slackware-14.1 server I
have
/usr/bin/postgres and assume it is for the 9.5 release since that's running
and the 9.6 release is initiated but not invoked. The data directories are
easy but where do I look for the two bindirs?


Assuming the bindirs are in your $PATH:

aklaver@panda:~> whereis -f pg_ctl
pg_ctl: /usr/local/pgsql/bin/pg_ctl /usr/local/pgsql94/bin/pg_ctl

Even if only one is the $PATH:

aklaver@panda:~> whereis -f pg_ctl
pg_ctl: /usr/local/pgsql/bin/pg_ctl

you can usually figure out where the other is.



TIA,

Rich






--
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] Upgrade from 9.5.4 to 9.6.1

2016-11-15 Thread John R Pierce


On 11/15/2016 2:37 PM, Rich Shepard wrote:
  All my previous version upgrades were performed by running 
pg_dumpall in
the older version followed by running 'pgsql -f ...' to install the 
dumped
.sql file, usually because the upgrade jumped several versions. Now 
I'd like

to try the available postgres commands.

  The older version is installed in /var/lib/pgsql/9.5/data and I just
initiated the new version in /var/lib/pgsql/9.6/data. The earlier 
version is

currently running.

  Is pg_upgrade the recommended way to upgrade from one minor version 
to the

next? The 9.5 manual recommends this approach for _major_ upgrades (e.g.,
8.4.7 to 9.6.1), but not for _minor_ upgrades (e.g., 9.0.1 to 9.0.4). 
That's
a first digit upgrade and a third digit upgrade. Since 9.5.4 to 9.6.1 
is a

second digit upgrade I suppose it's semi-major, but in which upgrade camp
does it belong?



9.5 is considered a major version, 9.5.4 is a minor.this will change 
when 10 is released, from 10 on, major versions will be 10, 11, 12, ...




  The command is:

pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir

and I don't know where to find -b and -B. On my Slackware-14.1 server 
I have
/usr/bin/postgres and assume it is for the 9.5 release since that's 
running
and the 9.6 release is initiated but not invoked. The data directories 
are
easy but where do I look for the two bindirs? 


based on the data paths you gave, I'm guessing you're on a 
redhat/centos/fedora type distribution?   those put the binaries in 
/usr/pgsql-X.Y/bin for version X.Y





--
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] Upgrade from 9.5.4 to 9.6.1


  All my previous version upgrades were performed by running pg_dumpall in
the older version followed by running 'pgsql -f ...' to install the dumped
.sql file, usually because the upgrade jumped several versions. Now I'd like
to try the available postgres commands.

  The older version is installed in /var/lib/pgsql/9.5/data and I just
initiated the new version in /var/lib/pgsql/9.6/data. The earlier version is
currently running.

  Is pg_upgrade the recommended way to upgrade from one minor version to the
next? The 9.5 manual recommends this approach for _major_ upgrades (e.g.,
8.4.7 to 9.6.1), but not for _minor_ upgrades (e.g., 9.0.1 to 9.0.4). That's
a first digit upgrade and a third digit upgrade. Since 9.5.4 to 9.6.1 is a
second digit upgrade I suppose it's semi-major, but in which upgrade camp
does it belong?

  The command is:

pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir

and I don't know where to find -b and -B. On my Slackware-14.1 server I have
/usr/bin/postgres and assume it is for the 9.5 release since that's running
and the 9.6 release is initiated but not invoked. The data directories are
easy but where do I look for the two bindirs?

TIA,

Rich



--
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] upgrade to repmgr3

2016-08-15 Thread Pekka Rinne

hi

2016-08-04 16:19 GMT+03:00 Ian Barwick :

> Hi
>
> On 08/04/2016 05:57 PM, Pekka Rinne wrote:
>
>> hi!
>>
>> I have been using postgres 9.4 and repmgr2.0 combination and been doing
>> replication (hot standby). Now I'd like to start doing slot based
>> replication and have installed repmgr3 and exeuted the provided sql
>> scripts
>> and also added use_replication_slots=1 into repmgr.conf.
>>
>> The question is that what is the correct procedure to switch into using
>> slots (max_replication_slots) in this case as the system has been set up
>> already without them? Do I have to unregister and re-register all the
>> standbys? Hopefully re-clone could be avoided.
>>
>
> No reclone needed.
>
> What I tried was that I configured max_replication_hosts=5, restarted
>> master, created some slots using select * from
>> pg_create_physical_replication_slot(), configured one created slot
>> into recovery.conf in the slave. What I noticed was that replication
>> seemed
>> to be still working after this but in repl_nodes table slot_name remained
>> empty. Then I did standby re-register with force and slot_name was filled
>> with repmgr_slot_2 value which is not the name I gave for the slot. I
>> think
>> repmgr invented this name but in the pg_replication_slots table
>> repmgr_slot_2 does not exist. There is only the slot I created myself
>> (active=t). So I guess this approach is not quite right.
>>
>> What if I just skip doing the re-register. Does is matter if slot_name
>> remains empty in repl_nodes?
>>
>
> This won't affect replication, however if you attempt any failover actions
> using repmgr (e.g. following a new master), it will probably cause
> problems when
> attempting to create a replication slot on the new master.
>
> As a workaround you can manually update the slot name in the repl_nodes
> table
> to match the one you've chosen. We'll update repmgr to better handle this
> kind of situation. I don't think we've had this particular use-case before,
> so I'll add some notes to the documentation on how best to handle it.
>
>
I tried this workaround. But what I noticed was that after promoting a
standby as a master my replication slots were renamed in repl_nodes to
repmgr_slot_2 and repmgr_slot_3. I did not use those names while creating
them.

Is the correct procedure to assume that nodes slot name should be
repmgr_slot_? This seems to be the case and slots can be created
following this rule.

Another thing is that is it mandatory to pre-create the slots by using
pg_create_physical_replication_slot()? Even if I do not do that replication
seems to sill be working but pg_replication_slots table remains empty. It
is only after promoting a standby as a master that one slot seems to get
created automatically.

br,
Pekka

Re: [GENERAL] upgrade to repmgr3

2016-08-11 Thread Martín Marqués

Hi,

2016-08-11 7:54 GMT-03:00 Pekka Rinne :
>>
>> Do you by chance have synchronous replication set? That ps output alone
>> doesn't say much, but being stuck on COMMIT normally points to failure
>> to sync the replication on a standby.
>>
>
> Yeah, I learned that repmgr3 actually writes to DB during promote. Repmgr2
> does not do that. And if the failed master itself is on the synchronized
> replicas list then the promote command hangs in commit as its not possible
> to sync to that failed node. Solution seemed to be to temporarily comment
> out synchronized replicas from postgresql.conf in new master and reload
> configfile. Then the promote command returns in command line.

Solution would be having at least 2 standbys listed in
synchronous_standby_names. With only one node name there, if the
standby goes down, all the transactions that make changes will hang at
commit execution.

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] upgrade to repmgr3

2016-08-11 Thread Pekka Rinne

hi

2016-08-09 15:39 GMT+03:00 Martín Marqués :

> Hi,
>
> El 08/08/16 a las 05:57, Pekka Rinne escribió:
> >
> > Meanwhile I did some more testing with my environment using repmgr3 and
> > noticed an issue with promoting standby node. Here is roughly what I did.
> >
> > 1. Install repmgr3.1.2 RPM to all nodes as upgrade to previous 2.0.2.
> > 2. I took repmgr release upgrade SQL-scripts from github 3.1 stable and
> > ran those on master (all three scripts in order).
> > 3. on master I stopped postgresql service
> > 4. on standby I said standby promote which does some things and then
> > hangs forever.
> >
> > This standby promote was working fine before repmgr upgrade.
> >
> > There is a COMMIT command visible with ps:
> >
> > 3324 ?Ss 0:00 postgres: repmgr repmgr (43666)
> > COMMIT waiting for 2/4E000548
>
> You mean it doesn't release the execution and give back the prompt?
>
> Do you by chance have synchronous replication set? That ps output alone
> doesn't say much, but being stuck on COMMIT normally points to failure
> to sync the replication on a standby.
>
>
Yeah, I learned that repmgr3 actually writes to DB during promote. Repmgr2
does not do that. And if the failed master itself is on the synchronized
replicas list then the promote command hangs in commit as its not possible
to sync to that failed node. Solution seemed to be to temporarily comment
out synchronized replicas from postgresql.conf in new master and reload
configfile. Then the promote command returns in command line.

br,
Pekka

Re: [GENERAL] upgrade to repmgr3

2016-08-09 Thread Martín Marqués

Hi,

El 08/08/16 a las 05:57, Pekka Rinne escribió:
> 
> Meanwhile I did some more testing with my environment using repmgr3 and
> noticed an issue with promoting standby node. Here is roughly what I did.
> 
> 1. Install repmgr3.1.2 RPM to all nodes as upgrade to previous 2.0.2.
> 2. I took repmgr release upgrade SQL-scripts from github 3.1 stable and
> ran those on master (all three scripts in order).
> 3. on master I stopped postgresql service
> 4. on standby I said standby promote which does some things and then
> hangs forever.
> 
> This standby promote was working fine before repmgr upgrade.
> 
> There is a COMMIT command visible with ps:
> 
> 3324 ?Ss 0:00 postgres: repmgr repmgr (43666)
> COMMIT waiting for 2/4E000548

You mean it doesn't release the execution and give back the prompt?

Do you by chance have synchronous replication set? That ps output alone
doesn't say much, but being stuck on COMMIT normally points to failure
to sync the replication on a standby.

> [2016-08-08 10:29:03] [DEBUG] get_pg_setting(): returned value is
> "/var/lib/pgsql/data"
> [2016-08-08 10:29:03] [NOTICE] promoting server using '/usr/bin/pg_ctl
> -D /var/lib/pgsql/data promote'
> server promoting

Here, it runs the promote command.

> [2016-08-08 10:29:03] [INFO] reconnecting to promoted server
> [2016-08-08 10:29:03] [DEBUG] connecting to: 'host=
> user=repmgr dbname=repmgr fallback_application_name='repmgr''
> [2016-08-08 10:29:03] [DEBUG] is_standby(): SELECT
> pg_catalog.pg_is_in_recovery()
> [2016-08-08 10:29:05] [DEBUG] is_standby(): SELECT
> pg_catalog.pg_is_in_recovery()
> [2016-08-08 10:29:05] [DEBUG] setting node 2 as master and marking
> existing master as failed

At this point, the promoted standby is a primary server (master) and
repmgr will then update the nodes table to reflect that:

> [2016-08-08 10:29:05] [DEBUG] begin_transaction()
> [2016-08-08 10:29:05] [DEBUG] commit_transaction()

If this commit is what you are seeing stuck (from ps output from above),
the it's like that you have a mixture of synchronous_commit set on and
synchronous_standby_names having 1 standby which is not available.

What settings are you using for those 2 parameters?

> The system is left in a strange state after this. If I start postgresql
> again in old master node and issue cluster show it lists both nodes as
> masters.

That's not a surprise. This is called a brain split, something repmgr
doesn't fully take care of (we rely on other tools to do the fencing or
STONITH)

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] upgrade to repmgr3

2016-08-08 Thread Pekka Rinne

hello

Thanks for your comments. They are very helpful. If you have any draft
level documentation available of the upgrade procedure I would be very
interested in seeing it and maybe trying it out as well.

Meanwhile I did some more testing with my environment using repmgr3 and
noticed an issue with promoting standby node. Here is roughly what I did.

1. Install repmgr3.1.2 RPM to all nodes as upgrade to previous 2.0.2.
2. I took repmgr release upgrade SQL-scripts from github 3.1 stable and ran
those on master (all three scripts in order).
3. on master I stopped postgresql service
4. on standby I said standby promote which does some things and then hangs
forever.

This standby promote was working fine before repmgr upgrade.

There is a COMMIT command visible with ps:

3324 ?Ss 0:00 postgres: repmgr repmgr (43666)
COMMIT waiting for 2/4E000548

What I see in the console is here:

[2016-08-08 10:29:03] [NOTICE] using configuration file
"/var/lib/pgsql/repmgr/repmgr.conf"
[2016-08-08 10:29:03] [INFO] connecting to standby database
[2016-08-08 10:29:03] [DEBUG] connecting to: 'host=
user=repmgr dbname=repmgr fallback_application_name='repmgr''
[2016-08-08 10:29:03] [INFO] connected to standby, checking its state
[2016-08-08 10:29:03] [DEBUG] is_standby(): SELECT
pg_catalog.pg_is_in_recovery()
[2016-08-08 10:29:03] [INFO] retrieving node list for cluster 'sensorlog'
[2016-08-08 10:29:03] [DEBUG] get_master_connection():
  SELECT id, conninfo,  CASE WHEN type = 'master' THEN 1 ELSE 2 END
AS type_priorityFROM "repmgr_sensorlog".repl_nodesWHERE cluster =
'sensorlog'  AND type != 'witness' ORDER BY active DESC, type_priority,
priority, id
[2016-08-08 10:29:03] [INFO] checking role of cluster node '1'
[2016-08-08 10:29:03] [DEBUG] connecting to: 'host=
user=repmgr dbname=repmgr fallback_application_name='repmgr''
[2016-08-08 10:29:03] [ERROR] connection to database failed: could not
connect to server: Connection refused
Is the server running on host "" and accepting
TCP/IP connections on port 5432?
[2016-08-08 10:29:03] [INFO] checking role of cluster node '2'
[2016-08-08 10:29:03] [DEBUG] connecting to: 'host=
user=repmgr dbname=repmgr fallback_application_name='repmgr''
[2016-08-08 10:29:03] [DEBUG] is_standby(): SELECT
pg_catalog.pg_is_in_recovery()
[2016-08-08 10:29:03] [INFO] checking role of cluster node '3'
[2016-08-08 10:29:03] [DEBUG] connecting to: 'host=
user=repmgr dbname=repmgr fallback_application_name='repmgr''
[2016-08-08 10:29:03] [DEBUG] is_standby(): SELECT
pg_catalog.pg_is_in_recovery()
[2016-08-08 10:29:03] [NOTICE] promoting standby
[2016-08-08 10:29:03] [DEBUG] get_pg_setting(): SELECT name, setting  FROM
pg_settings WHERE name = 'data_directory'
[2016-08-08 10:29:03] [DEBUG] get_pg_setting(): returned value is
"/var/lib/pgsql/data"
[2016-08-08 10:29:03] [NOTICE] promoting server using '/usr/bin/pg_ctl -D
/var/lib/pgsql/data promote'
server promoting
[2016-08-08 10:29:03] [INFO] reconnecting to promoted server
[2016-08-08 10:29:03] [DEBUG] connecting to: 'host=
user=repmgr dbname=repmgr fallback_application_name='repmgr''
[2016-08-08 10:29:03] [DEBUG] is_standby(): SELECT
pg_catalog.pg_is_in_recovery()
[2016-08-08 10:29:05] [DEBUG] is_standby(): SELECT
pg_catalog.pg_is_in_recovery()
[2016-08-08 10:29:05] [DEBUG] setting node 2 as master and marking existing
master as failed
[2016-08-08 10:29:05] [DEBUG] begin_transaction()
[2016-08-08 10:29:05] [DEBUG] commit_transaction()

The system is left in a strange state after this. If I start postgresql
again in old master node and issue cluster show it lists both nodes as
masters.

In this experiment I did not take slot based replication into use at all.
Everything is left just as it was before except the repmgr upgrade.

br,
Pekka

2016-08-04 16:19 GMT+03:00 Ian Barwick :

> Hi
>
> On 08/04/2016 05:57 PM, Pekka Rinne wrote:
>
>> hi!
>>
>> I have been using postgres 9.4 and repmgr2.0 combination and been doing
>> replication (hot standby). Now I'd like to start doing slot based
>> replication and have installed repmgr3 and exeuted the provided sql
>> scripts
>> and also added use_replication_slots=1 into repmgr.conf.
>>
>> The question is that what is the correct procedure to switch into using
>> slots (max_replication_slots) in this case as the system has been set up
>> already without them? Do I have to unregister and re-register all the
>> standbys? Hopefully re-clone could be avoided.
>>
>
> No reclone needed.
>
> What I tried was that I configured max_replication_hosts=5, restarted
>> master, created some slots using select * from
>> pg_create_physical_replication_slot(), configured one created slot
>> into recovery.conf in the slave. What I noticed was that replication
>> seemed
>> to be still working after this but in repl_nodes table slot_name remained
>> empty. Then I did standby re-register with force and slot_name was filled
>> with repmgr_slot_2 value which is not the

Re: [GENERAL] upgrade to repmgr3

2016-08-04 Thread Ian Barwick


Hi

On 08/04/2016 05:57 PM, Pekka Rinne wrote:

hi!

I have been using postgres 9.4 and repmgr2.0 combination and been doing
replication (hot standby). Now I'd like to start doing slot based
replication and have installed repmgr3 and exeuted the provided sql scripts
and also added use_replication_slots=1 into repmgr.conf.

The question is that what is the correct procedure to switch into using
slots (max_replication_slots) in this case as the system has been set up
already without them? Do I have to unregister and re-register all the
standbys? Hopefully re-clone could be avoided.


No reclone needed.


What I tried was that I configured max_replication_hosts=5, restarted
master, created some slots using select * from
pg_create_physical_replication_slot(), configured one created slot
into recovery.conf in the slave. What I noticed was that replication seemed
to be still working after this but in repl_nodes table slot_name remained
empty. Then I did standby re-register with force and slot_name was filled
with repmgr_slot_2 value which is not the name I gave for the slot. I think
repmgr invented this name but in the pg_replication_slots table
repmgr_slot_2 does not exist. There is only the slot I created myself
(active=t). So I guess this approach is not quite right.

What if I just skip doing the re-register. Does is matter if slot_name
remains empty in repl_nodes?


This won't affect replication, however if you attempt any failover actions
using repmgr (e.g. following a new master), it will probably cause problems when
attempting to create a replication slot on the new master.

As a workaround you can manually update the slot name in the repl_nodes table
to match the one you've chosen. We'll update repmgr to better handle this
kind of situation. I don't think we've had this particular use-case before,
so I'll add some notes to the documentation on how best to handle it.


Regards

Ian Barwick

--
 Ian Barwick   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] upgrade to repmgr3

2016-08-04 Thread Pekka Rinne

hi!

I have been using postgres 9.4 and repmgr2.0 combination and been doing
replication (hot standby). Now I'd like to start doing slot based
replication and have installed repmgr3 and exeuted the provided sql scripts
and also added use_replication_slots=1 into repmgr.conf.

The question is that what is the correct procedure to switch into using
slots (max_replication_slots) in this case as the system has been set up
already without them? Do I have to unregister and re-register all the
standbys? Hopefully re-clone could be avoided.

What I tried was that I configured max_replication_hosts=5, restarted
master, created some slots using select * from
pg_create_physical_replication_slot(), configured one created slot
into recovery.conf in the slave. What I noticed was that replication seemed
to be still working after this but in repl_nodes table slot_name remained
empty. Then I did standby re-register with force and slot_name was filled
with repmgr_slot_2 value which is not the name I gave for the slot. I think
repmgr invented this name but in the pg_replication_slots table
repmgr_slot_2 does not exist. There is only the slot I created myself
(active=t). So I guess this approach is not quite right.

What if I just skip doing the re-register. Does is matter if slot_name
remains empty in repl_nodes?

br,
Pekka

[GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

2016-02-04 Thread Karl Denninger

$ initdb -D data-default
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory data-default ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers ... 400kB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in data-default/base/1 ... FATAL:  could not
create semaphores: Invalid argument
DETAIL:  Failed system call was semget(2, 17, 03600).
child process exited with exit code 1
initdb: removing contents of data directory "data-default"
$
$ sysctl -a|grep semm
kern.ipc.semmsl: 512
kern.ipc.semmnu: 256
kern.ipc.semmns: 512
kern.ipc.semmni: 256

The system is running 9.4 just fine and the kernel configuration
requirements shouldn't have changed for semaphores should they?

-- 
Karl Denninger
k...@denninger.net 
/The Market Ticker/
/[S/MIME encrypted email preferred]/


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

2016-02-04 Thread Tom Lane

I wrote:
> Karl Denninger  writes:
>> $ initdb -D data-default
>> ...
>> creating template1 database in data-default/base/1 ... FATAL:  could not
>> create semaphores: Invalid argument
>> DETAIL:  Failed system call was semget(2, 17, 03600).

> Hmm.  On my Linux box, "man semget" says EINVAL means

>EINVAL nsems  is less than 0 or greater than the limit on the number of
>   semaphores per semaphore set (SEMMSL), or a semaphore set corre-
>   sponding  to  key  already  exists, and nsems is larger than the
>   number of semaphores in that set.

> which agrees with the POSIX spec.  Is FreeBSD the same?

BTW, looking at the code, I see that during initdb we would have tried
semaphore key 1 before 2.  So presumably, on key 1 we got an error code
that we recognized as meaning "semaphore set already exists", but then on
key 2 we got EINVAL instead.  That makes this even more curious.  I'd
be interested to see what "ipcs -s" says, if you have that command.
(You might need to run it as root to be sure it will show all sempaphores.)

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] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

2016-02-04 Thread Karl Denninger



On 2/4/2016 12:47, Tom Lane wrote:
> I wrote:
>> Karl Denninger  writes:
>>> $ initdb -D data-default
>>> ...
>>> creating template1 database in data-default/base/1 ... FATAL:  could not
>>> create semaphores: Invalid argument
>>> DETAIL:  Failed system call was semget(2, 17, 03600).
>> Hmm.  On my Linux box, "man semget" says EINVAL means
>>EINVAL nsems  is less than 0 or greater than the limit on the number 
>> of
>>   semaphores per semaphore set (SEMMSL), or a semaphore set 
>> corre-
>>   sponding  to  key  already  exists, and nsems is larger than 
>> the
>>   number of semaphores in that set.
>> which agrees with the POSIX spec.  Is FreeBSD the same?
> BTW, looking at the code, I see that during initdb we would have tried
> semaphore key 1 before 2.  So presumably, on key 1 we got an error code
> that we recognized as meaning "semaphore set already exists", but then on
> key 2 we got EINVAL instead.  That makes this even more curious.  I'd
> be interested to see what "ipcs -s" says, if you have that command.
> (You might need to run it as root to be sure it will show all sempaphores.)
>
>   regards, tom lane
There was indeed a "2" key out by the web server process; I shut it down
and cleared it and the upgrade is now running

Also filed a kernel bug with the FreeBSD folks against 10.2-STABLE as
the man page says you should have gotten back EEXIST.


-- 
Karl Denninger
k...@denninger.net 
/The Market Ticker/
/[S/MIME encrypted email preferred]/


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

2016-02-04 Thread Adrian Klaver


On 02/04/2016 10:02 AM, Karl Denninger wrote:

$ initdb -D data-default
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory data-default ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers ... 400kB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in data-default/base/1 ... FATAL:  could not
create semaphores: Invalid argument
DETAIL:  Failed system call was semget(2, 17, 03600).
child process exited with exit code 1
initdb: removing contents of data directory "data-default"
$
$ sysctl -a|grep semm
kern.ipc.semmsl: 512
kern.ipc.semmnu: 256
kern.ipc.semmns: 512
kern.ipc.semmni: 256

The system is running 9.4 just fine and the kernel configuration
requirements shouldn't have changed for semaphores should they?



Where did the 9.5 version come from and was it the same source as the 
9.4 version?




--
Karl Denninger
k...@denninger.net 
/The Market Ticker/
/[S/MIME encrypted email preferred]/



--
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] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

2016-02-04 Thread Tom Lane

Karl Denninger  writes:
> $ initdb -D data-default
> ...
> creating template1 database in data-default/base/1 ... FATAL:  could not
> create semaphores: Invalid argument
> DETAIL:  Failed system call was semget(2, 17, 03600).

Hmm.  On my Linux box, "man semget" says EINVAL means

   EINVAL nsems  is less than 0 or greater than the limit on the number of
  semaphores per semaphore set (SEMMSL), or a semaphore set corre-
  sponding  to  key  already  exists, and nsems is larger than the
  number of semaphores in that set.

which agrees with the POSIX spec.  Is FreeBSD the same?

Proceeding on the assumption that it is ...

17 is the same nsems value we've been using for donkey's years, so the
SEMMSL aspect of this seems unlikely to apply; what presumably is
happening is a collision with an existing semaphore's key.  Our code is
prepared for that, but it expects a different error code in such cases,
either EEXIST or EACCES:

/*
 * Fail quietly if error indicates a collision with existing set. One
 * would expect EEXIST, given that we said IPC_EXCL, but perhaps we
 * could get a permission violation instead?  Also, EIDRM might occur
 * if an old set is slated for destruction but not gone yet.
 */

It sounds like your kernel is returning EINVAL in preference to any of
those codes, which would be pretty broken.  I do not want to make our code
treat EINVAL as meaning we should retry with a different key, because if
the problem is indeed the SEMMSL limit, we'd be in an infinite loop.

You can probably get past this for the moment if you can remove the
semaphore set with key 2, but I'd advise filing a FreeBSD kernel
bug about their choice of errno.

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] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

2016-02-04 Thread Adrian Klaver

On 02/04/2016 10:02 AM, Karl Denninger wrote:

$ initdb -D data-default
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory data-default ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers ... 400kB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in data-default/base/1 ... FATAL: could not
create semaphores: Invalid argument
DETAIL: Failed system call was semget(2, 17, 03600).
child process exited with exit code 1
initdb: removing contents of data directory "data-default"
$
$ sysctl -a|grep semm
kern.ipc.semmsl: 512
kern.ipc.semmnu: 256
kern.ipc.semmns: 512
kern.ipc.semmni: 256

The system is running 9.4 just fine and the kernel configuration
requirements shouldn't have changed for semaphores should they?

If it helps, the hint from the source code:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/port/sysv_sema.c;h=f6f15169200a03e9da46ae348994f04297d22017;hb=HEAD

"This error does *not* mean that you have run out of disk space. It
occurs when either the system limit for the maximum number of semaphore
sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS),
would be exceeded. You need to raise the respective kernel parameter.
Alternatively, reduce PostgreSQL's consumption of semaphores by reducing
its max_connections parameter.
The PostgreSQL documentation contains more information about configuring
your system for PostgreSQL."

--
Karl Denninger
k...@denninger.net
/The Market Ticker/
/[S/MIME encrypted email preferred]/

--
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] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

2016-02-04 Thread Karl Denninger



On 2/4/2016 12:28, Tom Lane wrote:
> Karl Denninger  writes:
>> $ initdb -D data-default
>> ...
>> creating template1 database in data-default/base/1 ... FATAL:  could not
>> create semaphores: Invalid argument
>> DETAIL:  Failed system call was semget(2, 17, 03600).
> Hmm.  On my Linux box, "man semget" says EINVAL means
>
>EINVAL nsems  is less than 0 or greater than the limit on the number of
>   semaphores per semaphore set (SEMMSL), or a semaphore set corre-
>   sponding  to  key  already  exists, and nsems is larger than the
>   number of semaphores in that set.
>
> which agrees with the POSIX spec.  Is FreeBSD the same?
>
> Proceeding on the assumption that it is ...
>
> 17 is the same nsems value we've been using for donkey's years, so the
> SEMMSL aspect of this seems unlikely to apply; what presumably is
> happening is a collision with an existing semaphore's key.  Our code is
> prepared for that, but it expects a different error code in such cases,
> either EEXIST or EACCES:
>
> /*
>  * Fail quietly if error indicates a collision with existing set. One
>  * would expect EEXIST, given that we said IPC_EXCL, but perhaps we
>  * could get a permission violation instead?  Also, EIDRM might occur
>  * if an old set is slated for destruction but not gone yet.
>  */
>
> It sounds like your kernel is returning EINVAL in preference to any of
> those codes, which would be pretty broken.  I do not want to make our code
> treat EINVAL as meaning we should retry with a different key, because if
> the problem is indeed the SEMMSL limit, we'd be in an infinite loop.
>
> You can probably get past this for the moment if you can remove the
> semaphore set with key 2, but I'd advise filing a FreeBSD kernel
> bug about their choice of errno.
>
>   regards, tom lane

That sounds like it well may be the problem

s655642 --rw-rw-rw- www  www  www 
www 3 12:29:14  7:56:04


Oh look, the web server process has a semaphore set out with a "2" key

I've filed a bug report.  Thanks.

-- 
Karl Denninger
k...@denninger.net 
/The Market Ticker/
/[S/MIME encrypted email preferred]/


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] Upgrade from 9.3 to 9.4 issue

2015-10-31 Thread Stephen Davies


On 31/10/15 13:32, Tom Lane wrote:

Stephen Davies  writes:

I have just upgraded from Fedora 21 to 22.
This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes postmaster
to fail because the existing databases are still at 9.3.
As suggested, I then ran postgresql-setup --upgrade but this failed with:
lc_collate cluster values do not match:  old "en_US.UTF-8", new "en_AU.UTF-8"


Hm, apparently you changed the system-wide language setting between F21
and F22?

One thing you should do is file a bug in Red Hat's bugzilla, pointing out
that postgresql-setup needs to endeavor to create the new cluster with
lc_collate and lc_ctype matching the old one.

Then, depending on what you want to do:

1. You really want to switch to en_AU, not just system-wide but for the
database: gonna have to dump and reload, I'm afraid.  pg_upgrade will
not handle this scenario.

2. You want to switch to en_AU system-wide but it's okay to leave the
database running in en_US: what I'd do personally is edit the
postgresql-setup shell script and add "export LANG=en_US.UTF-8" just
before the initdb call.  This won't stick across your next Fedora
upgrade, but hopefully by then Red Hat will have addressed your bug.

3. This was a mistake and you'd rather stay in en_US all round:
you can probably change the system-wide language setting somewhere,
but I do not remember where right at the moment.

regards, tom lane


I have resolved this issue.
I chose your option 2 (almost).
I couldn't get postgresql-setup to do what I wanted so I reverted to doing the 
upgrade by hand.
Luckily, PostgreSQL 9.3 had not been removed from the box so I was able to 
manually initdb with the US collation etc to create a new cluster where I 
wanted it to be and then run a regular pg_upgrade.

This is actually easier than mucking about with postgesql-setup.

--
=
Stephen Davies Consulting P/L Phone: 08-8177 1595
Adelaide, South Australia.Mobile:040 304 0583
Records & Collections Management.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Upgrade from 9.3 to 9.4 issue

2015-10-30 Thread Stephen Davies


I have just upgraded from Fedora 21 to 22.
This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes postmaster 
to fail because the existing databases are still at 9.3.

As suggested, I then ran postgresql-setup --upgrade but this failed with:

Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is a superuser   ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* system OID user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok
Checking for invalid "line" user columnsok
Creating dump of global objects ok
Creating dump of database schemas
  books
  postgres
  pots
  stocks
  template1
ok

lc_collate cluster values do not match:  old "en_US.UTF-8", new "en_AU.UTF-8"
Failure, exiting

How can I recover from here?

Cheers and thanks,
Stephen Davies


--
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] Upgrade from 9.3 to 9.4 issue

2015-10-30 Thread Tom Lane

Stephen Davies  writes:
> I have just upgraded from Fedora 21 to 22.
> This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes 
> postmaster 
> to fail because the existing databases are still at 9.3.
> As suggested, I then ran postgresql-setup --upgrade but this failed with:
> lc_collate cluster values do not match:  old "en_US.UTF-8", new "en_AU.UTF-8"

Hm, apparently you changed the system-wide language setting between F21
and F22?

One thing you should do is file a bug in Red Hat's bugzilla, pointing out
that postgresql-setup needs to endeavor to create the new cluster with
lc_collate and lc_ctype matching the old one.

Then, depending on what you want to do:

1. You really want to switch to en_AU, not just system-wide but for the
database: gonna have to dump and reload, I'm afraid.  pg_upgrade will
not handle this scenario.

2. You want to switch to en_AU system-wide but it's okay to leave the
database running in en_US: what I'd do personally is edit the
postgresql-setup shell script and add "export LANG=en_US.UTF-8" just
before the initdb call.  This won't stick across your next Fedora
upgrade, but hopefully by then Red Hat will have addressed your bug.

3. This was a mistake and you'd rather stay in en_US all round:
you can probably change the system-wide language setting somewhere,
but I do not remember where right at the moment.

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] Upgrade from postgresql-9.5alpha1 to postgresql-9.5alpha2 (Debian packages)

2015-08-18 Thread Peter J. Holzer

It looks like the catalog version has changed between 9.5alpha1 and
9.5alpha2:

 FATAL:  database files are incompatible with server
 DETAIL:  The database cluster was initialized with CATALOG_VERSION_NO 
201506282,
  but the server was compiled with CATALOG_VERSION_NO 201507281.
 HINT:  It looks like you need to initdb.

Normally, one would use pg_upgradecluster to do the upgrade, but
pg_upgradecluster assumes that the old and new version are installed in
parallel. Likewise, the low-level tool pg_upgrade needs the old bindir,
if I read the man-page correctly, and of course, apt-get upgrade
overwrites that, since it's just two versions of the same package
(unlike a major upgrade which is a new package). 

So, what's the best way to do the upgrade?

* Copy the bindir before the upgrade (or restore from backup) to a safe
  place and do pg_upgrade?
* Initdb a new cluster and restore yesterdays backup? 
* Something else?

hp

-- 
   _  | Peter J. Holzer| I want to forget all about both belts and
|_|_) || suspenders; instead, I want to buy pants 
| |   | h...@hjp.at | that actually fit.
__/   | http://www.hjp.at/ |   -- http://noncombatant.org/


signature.asc
Description: Digital signature

Re: [GENERAL] Upgrade from postgresql-9.5alpha1 to postgresql-9.5alpha2 (Debian packages)

2015-08-18 Thread Masahiko Sawada

On Tue, Aug 18, 2015 at 6:06 PM, Peter J. Holzer hjp-pg...@hjp.at wrote:
 It looks like the catalog version has changed between 9.5alpha1 and
 9.5alpha2:

  FATAL:  database files are incompatible with server
  DETAIL:  The database cluster was initialized with CATALOG_VERSION_NO 
 201506282,
   but the server was compiled with CATALOG_VERSION_NO 201507281.
  HINT:  It looks like you need to initdb.

 Normally, one would use pg_upgradecluster to do the upgrade, but
 pg_upgradecluster assumes that the old and new version are installed in
 parallel. Likewise, the low-level tool pg_upgrade needs the old bindir,
 if I read the man-page correctly, and of course, apt-get upgrade
 overwrites that, since it's just two versions of the same package
 (unlike a major upgrade which is a new package).

 So, what's the best way to do the upgrade?

 * Copy the bindir before the upgrade (or restore from backup) to a safe
   place and do pg_upgrade?
 * Initdb a new cluster and restore yesterdays backup?
 * Something else?


I've not used pg_upgrade at such case, but If you have a enough time
to do upgrading, I will suggest to take backup(pg_dumpall) from
current cluster and then restore it to new cluster.
I think pg_upgrade is basically used at major version upgrading.

Regards,

--
Masahiko Sawada


-- 
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] Upgrade from postgresql-9.5alpha1 to postgresql-9.5alpha2 (Debian packages)

2015-08-18 Thread Peter J. Holzer

On 2015-08-18 20:40:10 +0900, Masahiko Sawada wrote:
 On Tue, Aug 18, 2015 at 6:06 PM, Peter J. Holzer hjp-pg...@hjp.at wrote:
  It looks like the catalog version has changed between 9.5alpha1 and
  9.5alpha2:
[...]
  So, what's the best way to do the upgrade?
 
  * Copy the bindir before the upgrade (or restore from backup) to a safe
place and do pg_upgrade?
  * Initdb a new cluster and restore yesterdays backup?
  * Something else?
 
 
 I've not used pg_upgrade at such case, but If you have a enough time
 to do upgrading, I will suggest to take backup(pg_dumpall) from
 current cluster and then restore it to new cluster.

Of course you would have to make a backup before the upgrade to restore
it afterwards. I could of course have forcibly downgraded to alpha1
again and made a new backup, but since this is a test system I just
decided to drop and recreate the cluster and restore yesterday's backup.
(One of my colleagues won't be too pleased about that, I think)

 I think pg_upgrade is basically used at major version upgrading.

This was basically a major version upgrade, the problem was that it
wasn't reflected in the package/directory structure (normally the
postgres debian packages are quite fastidious in separating everything
so that you have both an old and a new installation in the places where
pg_upgradecluster expects them), and that I didn't expect it (the
possibility of catalog version changes from one alpha release to the
next was discussed before the release of alpha1, but I somehow
classified that as theoretically possible but not likely - my fault),
and finally that I don't really understand the finer points of
pg_upgrade (I managed to use it in a similar situation some time ago,
but I had to read the source code of pg_upgradecluster (and I think I
even single-stepped through it in the debugger) to figure out the
parameters and unfortunately I didn't take notes). 

No big harm done (alpha software on a test system - I expect things to
blow up once in a while), but maybe the person preparing the alpha
releases can figure out how to make the upgrade smoother. At least a
warning in the release announcement would be nice (wouldn't have helped
me as I have to admit that I read that only after I upgraded, but it
would help those who do things in the right order ;-) ).

hp

-- 
   _  | Peter J. Holzer| I want to forget all about both belts and
|_|_) || suspenders; instead, I want to buy pants 
| |   | h...@hjp.at | that actually fit.
__/   | http://www.hjp.at/ |   -- http://noncombatant.org/


signature.asc
Description: Digital signature

[GENERAL] Upgrade postgres cluster on FreeBSD using pg_upgrade

2015-07-19 Thread Amitabh Kant

Hi

I need to upgrade a PG cluster from 9.1 to 9.4 running on dedicated FreeBSD
9.2 server. Earlier I followed the simple pg_dump / pg_restore, but now
that the cluster has grown to around 700 GB (1 TB total HD size), I am now
inclined towards using pg_upgrade.

pg_upgrade requires both binary to be present at the same time, which seems
difficult as I have installed using ports. I don't want to compile PG
myself unless that is the only way out. Reading the mailing lists and
previous questions, I see following options:

a) Use the work directory of port to compile the binaries and use it with
pg_upgrade

b) use jails as noted in some of the discussions online, however no idea
whether it would work in my case.

c) Somehow modify the ports options so each versions binary is installed
separately. I have no idea how to do it, maybe need to ask this on the
FreeBSD mailing list.

What is the route generally preferred by those running PG on FreeBSD? Is
there something simple which I have missed out?


With regards

Amitabh

Re: [GENERAL] Upgrade postgres cluster on FreeBSD using pg_upgrade

2015-07-19 Thread Joseph Kregloh

It's actually simpler than you think. Here are the steps that I follow:

1. Install 9.4 in another directory, if you compile it from the ports make
suere you add the PREFIX flag. For example: cd
/usr/ports/databases/postgresql94-server/  make install clean
PREFIX=/opt, this will install 9.4 on the /opt directory.
2. Install the contrib the same way.
3. Initialize the 9.4 database in another directory /opt/bin/initdb -D
/usr/local/pgsql_94/data -E UTF8.
4. Make sure the two databases are compatible: /opt/bin/pg_upgrade -d
/usr/local/pgsql/data -D /usr/local/pgsql_94/data -b /usr/local/bin -B
/opt/bin -p 5001 -P 5002 -c
5. Once everything is complete, run the scripts to delete old data and
analyze the new db
6. Deinstall 9.0 and 9.4.
7. Install 9.4 using the defaults.
8. Move the /usr/local/pgsql_94/data to /usr/local/pgsql/data
9. Copy any settings you want to restore and that's it.

Above is the basic steps as I remember from memory. But I've successfully
upgraded many databases this way.

On Sun, Jul 19, 2015 at 1:35 PM, Amitabh Kant amitabhk...@gmail.com wrote:

 Hi

 I need to upgrade a PG cluster from 9.1 to 9.4 running on dedicated
 FreeBSD 9.2 server. Earlier I followed the simple pg_dump / pg_restore, but
 now that the cluster has grown to around 700 GB (1 TB total HD size), I am
 now inclined towards using pg_upgrade.

 pg_upgrade requires both binary to be present at the same time, which
 seems difficult as I have installed using ports. I don't want to compile PG
 myself unless that is the only way out. Reading the mailing lists and
 previous questions, I see following options:


Do you use pkg? Don't be afraid of building from source, give you more
options and you can see what is actually installed in terms of dependencies.


 a) Use the work directory of port to compile the binaries and use it with
 pg_upgrade


You install it in a different location.


 b) use jails as noted in some of the discussions online, however no idea
 whether it would work in my case.


Jails work, but in this case it would be more trouble. If you had it in a
jail already then that would be different story.


 c) Somehow modify the ports options so each versions binary is installed
 separately. I have no idea how to do it, maybe need to ask this on the
 FreeBSD mailing list.


This I explained above.


-Joseph Kregloh

Re: [GENERAL] Upgrade postgres cluster on FreeBSD using pg_upgrade

2015-07-19 Thread Amitabh Kant

On Mon, Jul 20, 2015 at 1:57 AM, Joseph Kregloh jkreg...@sproutloud.com
wrote:

 It's actually simpler than you think. Here are the steps that I follow:

 1. Install 9.4 in another directory, if you compile it from the ports make
 suere you add the PREFIX flag. For example: cd
 /usr/ports/databases/postgresql94-server/  make install clean
 PREFIX=/opt, this will install 9.4 on the /opt directory.
 2. Install the contrib the same way.
 3. Initialize the 9.4 database in another directory /opt/bin/initdb -D
 /usr/local/pgsql_94/data -E UTF8.
 4. Make sure the two databases are compatible: /opt/bin/pg_upgrade -d
 /usr/local/pgsql/data -D /usr/local/pgsql_94/data -b /usr/local/bin -B
 /opt/bin -p 5001 -P 5002 -c
 5. Once everything is complete, run the scripts to delete old data and
 analyze the new db
 6. Deinstall 9.0 and 9.4.
 7. Install 9.4 using the defaults.
 8. Move the /usr/local/pgsql_94/data to /usr/local/pgsql/data
 9. Copy any settings you want to restore and that's it.

 Above is the basic steps as I remember from memory. But I've successfully
 upgraded many databases this way.

 On Sun, Jul 19, 2015 at 1:35 PM, Amitabh Kant amitabhk...@gmail.com
 wrote:

 Hi

 I need to upgrade a PG cluster from 9.1 to 9.4 running on dedicated
 FreeBSD 9.2 server. Earlier I followed the simple pg_dump / pg_restore, but
 now that the cluster has grown to around 700 GB (1 TB total HD size), I am
 now inclined towards using pg_upgrade.

 pg_upgrade requires both binary to be present at the same time, which
 seems difficult as I have installed using ports. I don't want to compile PG
 myself unless that is the only way out. Reading the mailing lists and
 previous questions, I see following options:


 Do you use pkg? Don't be afraid of building from source, give you more
 options and you can see what is actually installed in terms of dependencies.


 a) Use the work directory of port to compile the binaries and use it with
 pg_upgrade


 You install it in a different location.


 b) use jails as noted in some of the discussions online, however no idea
 whether it would work in my case.


 Jails work, but in this case it would be more trouble. If you had it in a
 jail already then that would be different story.


 c) Somehow modify the ports options so each versions binary is installed
 separately. I have no idea how to do it, maybe need to ask this on the
 FreeBSD mailing list.


 This I explained above.


 -Joseph Kregloh


I had a feeling I was missing something simple. I was looking at DESTDIR
instead of PREFIX. I will try it out. Seems pretty elegant and simple.

I do use pkg on production servers, but I have my own pkg repository where
I build packages to be deployed across multiple servers.

Thanks for the help.


With regards

Amitabh

[GENERAL] Upgrade to 9.3 - performance issue ?

2014-08-08 Thread Karthik Iyer


Hello,

We were planing to upgrade from 9.0 to to 9.3.  While 
investigating on the performance side, we stumbled  upon a link which 
says there may be performance degradation on 9.3:


http://postgresql.1045698.n5.nabble.com/upgrade-from-9-2-x-to-9-3-causes-significant-performance-degradation-td5771288.html

So we are planning to do this post the upgrade:

echo never  /sys/kernel/mm/transparent_hugepage/enabled
echo never  /sys/kernel/mm/transparent_hugepage/defrag


Wanted to understand, If there are any other such tweaks that we need to 
take care of, before we go 9.3


Some details:

OS: CentOS 6.2
Kernel: 2.6.32-431.20.3.el6.x86_64


Help here is appreciated.

Thanks in advance,

- Karthik






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Upgrade to 9.3

2014-07-21 Thread Karthik Iyer


Hello,

We are planning to upgrade Postgres from 9.0 to 9.3. Was wondering 
if there are any serious changes that I have to look out for 
(syntax/datatypes changes) so that my code does not break.


Thanks in advance.

- Karthik


--
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] Upgrade to 9.3

2014-07-21 Thread Alan Hodgson

On Monday, July 21, 2014 09:05:18 PM Karthik Iyer wrote:
 Hello,
 
  We are planning to upgrade Postgres from 9.0 to 9.3. Was wondering
 if there are any serious changes that I have to look out for
 (syntax/datatypes changes) so that my code does not break.
 

http://www.postgresql.org/docs/9.1/static/release-9-1.html
http://www.postgresql.org/docs/9.3/static/release-9-2.html
http://www.postgresql.org/docs/9.3/static/release-9-3.html



-- 
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] Upgrade to 9.3

2014-07-21 Thread Christian Jensen

unsubscribe


On Mon, Jul 21, 2014 at 1:22 PM, Alan Hodgson ahodg...@simkin.ca wrote:

 On Monday, July 21, 2014 09:05:18 PM Karthik Iyer wrote:
  Hello,
 
   We are planning to upgrade Postgres from 9.0 to 9.3. Was wondering
  if there are any serious changes that I have to look out for
  (syntax/datatypes changes) so that my code does not break.
 

 http://www.postgresql.org/docs/9.1/static/release-9-1.html
 http://www.postgresql.org/docs/9.3/static/release-9-2.html
 http://www.postgresql.org/docs/9.3/static/release-9-3.html



 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general




-- 

*Christian Jensen*
2151 Burnside Road West
Unit #5
Victoria, BC V9B 0P5
+1 (778) 996-4283
christ...@jensenbox.com

[GENERAL] Upgrade: 9.0.5-9.4


  Postgresql-9.0.5 is installed in /usr/local/pgsql/ and -9.4's data/
subdirectory is installed in /var/lib/pgsql/9.4/ with the executable in
/usr/bin/.

  Running pg_upgrade fails at the end because of a locale difference:

lc_ctype cluster values do not match:  old C, new en_US.UTF-8
Failure, exiting

  How do I remedy this?

Thanks in advance,

Rich


--
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] Upgrade: 9.0.5-9.4

2014-03-21 Thread Sergey Konoplev

On Fri, Mar 21, 2014 at 9:34 AM, Rich Shepard rshep...@appl-ecosys.com wrote:
   Postgresql-9.0.5 is installed in /usr/local/pgsql/ and -9.4's data/
 subdirectory is installed in /var/lib/pgsql/9.4/ with the executable in
 /usr/bin/.

   Running pg_upgrade fails at the end because of a locale difference:

 lc_ctype cluster values do not match:  old C, new en_US.UTF-8
 Failure, exiting

   How do I remedy this?

Drop your newly created 9.4 cluster dir and re-init it with C locale like this:

initdb --locale=C -D ...

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@gmail.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] Upgrade: 9.0.5-9.4

2014-03-21 Thread Joshua D. Drake



On 03/21/2014 09:34 AM, Rich Shepard wrote:


   Postgresql-9.0.5 is installed in /usr/local/pgsql/ and -9.4's data/
subdirectory is installed in /var/lib/pgsql/9.4/ with the executable in
/usr/bin/.

   Running pg_upgrade fails at the end because of a locale difference:

lc_ctype cluster values do not match:  old C, new en_US.UTF-8
Failure, exiting

   How do I remedy this?


If you want to use pg_upgrade, then you are going to have to dump your 
old database, clean it to UTF-8 (with say iconv), then reimport it.


At that point you might as well just pull it into 9.3.

JD




Thanks in advance,

Rich





--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.


--
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] Upgrade: 9.0.5-9.4


On Fri, 21 Mar 2014, Sergey Konoplev wrote:


Drop your newly created 9.4 cluster dir and re-init it with C locale like this:


Sergey,

  How do I drop the cluster? I've not had need to do this before.


initdb --locale=C -D ...


  Do I want the former locale or the current one; does it make any
difference for the few databases in the cluster?

Thanks,

Rich

--
Richard B. Shepard, Ph.D.  |  Have knowledge, will travel.
Applied Ecosystem Services, Inc.   |
www.appl-ecosys.com  Voice: 503-667-4517 Fax: 503-667-8863


--
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] Upgrade: 9.0.5-9.4


On Fri, 21 Mar 2014, Joshua D. Drake wrote:


If you want to use pg_upgrade, then you are going to have to dump your old
database, clean it to UTF-8 (with say iconv), then reimport it.

At that point you might as well just pull it into 9.3.


Joshua,

  I did a pg_dumpall before starting this process. If I can use pg_restore
on the running 9.4 will that convert locales and get the job done?

Thanks,

Rich


--
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] Upgrade: 9.0.5-9.4


On Fri, 21 Mar 2014, Rich Shepard wrote:


 I did a pg_dumpall before starting this process. If I can use pg_restore
on the running 9.4 will that convert locales and get the job done?


  Let me be more specific. Can I get databases working again by doing these
steps?

1.)  Start the new -9.4 version running.

2.)  Run pg_restore on each database that's in the .sql file created by
pg_dumpall.

  I have 5 databases in the file, but only need to restore 4 to fully
working condition.

  If there's another process please tell me how to proceed.

Thanks,

Rich


--
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] Upgrade: 9.0.5-9.4


On Fri, 21 Mar 2014, Rich Shepard wrote:


1.)  Start the new -9.4 version running.


  It's now running.


2.)  Run pg_restore on each database that's in the .sql file created by
pg_dumpall.


  Will this work?

  psql -e -d template1 -f
/home/rshepard/data/database-backups/pg-9.0.5-2014-03.17.sql

(all on one line, of course; wrapped by alpine.)

Rich


--
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] Upgrade: 9.0.5-9.3.4 [RESOLVED]


On Fri, 21 Mar 2014, Rich Shepard wrote:


 psql -e -d template1 -f
/home/rshepard/data/database-backups/pg-9.0.5-2014-03-17.sql


  Yep.

  Now all databases are running on 9.3.4.

Thanks, all!

Rich


--
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] Upgrade: 9.0.5-9.3.3

2014-03-18 Thread Rich Shepard


On Mon, 17 Mar 2014, Rich Shepard wrote:


 Now, -9.0.5 is installed in /usr/local/pgsql/ and -9.3.3 is installed in
/opt/pgsql-9.3.3. I want to use pg_upgrade and have read the Web page with
the instructions.


  I am having problems initializing the new version in /opt/pgsql-9.3.3. I
kill the postgres-9.0.5 process, and as user 'postgres' I tried to initdb
with this result:

initdb -D /opt/pgsql-9.3.3/9.3/data/
The program postgres was found by /bin/initdb
but was not the same version as initdb.
Check your installation.

  When I ask which postgres I find:

[root@salmo ~]# ll /usr/bin/postgres 
lrwxrwxrwx 1 root root 34 Mar 17 06:50 /usr/bin/postgres -

../lib/postgresql/9.3/bin/postgres*

so I am confused on how I need to run initdb, or what I need to
rename/remove to initialize the new cluster so I can run pg_upgrade and move
the databases over.

Rich


--
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] Upgrade: 9.0.5-9.3.3

2014-03-18 Thread Tom Lane

Rich Shepard rshep...@appl-ecosys.com writes:
 On Mon, 17 Mar 2014, Rich Shepard wrote:
 Now, -9.0.5 is installed in /usr/local/pgsql/ and -9.3.3 is installed in
 /opt/pgsql-9.3.3. I want to use pg_upgrade and have read the Web page with
 the instructions.

I am having problems initializing the new version in /opt/pgsql-9.3.3. I
 kill the postgres-9.0.5 process, and as user 'postgres' I tried to initdb
 with this result:

 initdb -D /opt/pgsql-9.3.3/9.3/data/
 The program postgres was found by /bin/initdb
 but was not the same version as initdb.
 Check your installation.

When I ask which postgres I find:

 [root@salmo ~]# ll /usr/bin/postgres 
 lrwxrwxrwx 1 root root 34 Mar 17 06:50 /usr/bin/postgres -
 ../lib/postgresql/9.3/bin/postgres*

Your PATH seems to be finding initdb in /bin (or is that /usr/bin),
not the one you want under /opt/pgsql-9.3.3.

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] Upgrade: 9.0.5-9.3.3

2014-03-18 Thread Rich Shepard


On Tue, 18 Mar 2014, Tom Lane wrote:


Your PATH seems to be finding initdb in /bin (or is that /usr/bin), not
the one you want under /opt/pgsql-9.3.3.


Tom,

  Thanks for catching what I did not see. There was an initdb from 2011 in
/bin/ and the new one in /usr/bin/ is a softlink to
../lib/postgresql/9.3/bin/initdb*

  Removing the older initdb allowed the new one to run and successfully
complete.

Regards,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Upgrade: 9.0.5-9.3.3

2014-03-17 Thread Rich Shepard


  Now, -9.0.5 is installed in /usr/local/pgsql/ and -9.3.3 is installed in
/opt/pgsql-9.3.3. I want to use pg_upgrade and have read the Web page with
the instructions.

  Instruction #4 tells me to install pg_upgrade and pg_upgrade_support. I
have the 9.0.5 version of pg_upgrade in /opt/postgresql-9.0.5/contrib/
(without pg_upgrade_support). But, I do not see the 'contrib' page on
postgresql.org. I find the 'community' and 'foundry' pages, but not the
'contrib' page. A pointer to pg_upgrade and pg_upgrade_support for 9.3.3 is
needed.

  If my reading of the instructions is correct, the following command line
should migrate my few, small databased from 9.0.5 to 9.3.3:

pg_upgrade -b /usr/local/pgsql/bin -B /opt/pgsql-9.3.3/bin -d \
/usr/local/pgsql/data -D /opt/pgsql-9.3.3/data

  I don't see that I need any additional options. Do I?

TIA,

Rich


--
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] Upgrade: 9.0.5-9.3.3

2014-03-17 Thread Tom Lane

Rich Shepard rshep...@appl-ecosys.com writes:
Now, -9.0.5 is installed in /usr/local/pgsql/ and -9.3.3 is installed in
 /opt/pgsql-9.3.3. I want to use pg_upgrade and have read the Web page with
 the instructions.

I'm guessing from those path names that you are using self-compiled
executables, not somebody's packaging?  If the latter, whose?  I'm
confused as to how you got pg_upgrade installed without knowing where
it came from.

Instruction #4 tells me to install pg_upgrade and pg_upgrade_support. I
 have the 9.0.5 version of pg_upgrade in /opt/postgresql-9.0.5/contrib/
 (without pg_upgrade_support). But, I do not see the 'contrib' page on
 postgresql.org. I find the 'community' and 'foundry' pages, but not the
 'contrib' page. A pointer to pg_upgrade and pg_upgrade_support for 9.3.3 is
 needed.

contrib is part of the main PG distribution.  The sources for pg_upgrade
would be under contrib/pg_upgrade/ in an unpacked tarball, and
pg_upgrade_support lives next door in contrib/pg_upgrade_support/.
If you did a make install in contrib/ that would've installed both.

Possibly you're expecting pg_upgrade_support to produce an executable
under $installdir/bin?  It doesn't --- it should get installed as
lib/pg_upgrade_support.so or equivalent.  In any case, pg_upgrade will
certainly complain if it can't find it.

If my reading of the instructions is correct, the following command line
 should migrate my few, small databased from 9.0.5 to 9.3.3:

 pg_upgrade -b /usr/local/pgsql/bin -B /opt/pgsql-9.3.3/bin -d \
 /usr/local/pgsql/data -D /opt/pgsql-9.3.3/data

That's pretty much the base case.  If none of the other options appeal to
you, don't use 'em.

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] Upgrade: 9.0.5-9.3.3

2014-03-17 Thread Rich Shepard


On Mon, 17 Mar 2014, Tom Lane wrote:


I'm guessing from those path names that you are using self-compiled
executables, not somebody's packaging?  If the latter, whose?  I'm
confused as to how you got pg_upgrade installed without knowing where it
came from.


Tom,

  Both were buit with scripts from SlackBuilds.org. The 9.0.5 pg_upgrade in
/opt/postgresql-9.0.5/contrib/ is datestamped Sep 22, 2011; I don't see that
I actually built and installed it or pg_upgrade_support (that's also there
as source, but neither built nor installed). Three-and-a-half years later
I've no recollection from where I downloaded them.


contrib is part of the main PG distribution. The sources for pg_upgrade
would be under contrib/pg_upgrade/ in an unpacked tarball, and
pg_upgrade_support lives next door in contrib/pg_upgrade_support/. If you
did a make install in contrib/ that would've installed both.


  A-ha! Running 'pg_upgrade --help' tells me that it is, indeed, installed.
And, it is the version for -9.3.3, too. So, this question's been answered.


That's pretty much the base case. If none of the other options appeal to
you, don't use 'em.


  Nah, nothing looks like I need them.

Much appreciated,

Rich

--
Richard B. Shepard, Ph.D.  |  Have knowledge, will travel.
Applied Ecosystem Services, Inc.   |
www.appl-ecosys.com  Voice: 503-667-4517 Fax: 503-667-8863


--
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] Upgrade from 9.1 to 9.10

2013-10-22 Thread ramistuni

Thanks much. It is of great help.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Upgrade-from-9-1-to-9-10-tp5775290p5775534.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Upgrade from 9.1 to 9.10

2013-10-21 Thread ramistuni

Hi All, I am relatively new to postgres and need to upgrade postgres 9.1.1 to
9.1.10. As I understood from the documentation that Upgrading to a minor
release does not require a dump and restore; merely stop the database
server, install the updated binaries, and restart the server Is there any
step by step instructions available to do this kind of migration? We are
currently running PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by
gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit We have 3 servers in
production. Primary Standby (replication using streaming) Archive (uses
slony) Thanks in advance



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Upgrade-from-9-1-to-9-10-tp5775290.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Upgrade from 9.1 to 9.10

2013-10-21 Thread John R Pierce


On 10/21/2013 10:57 AM, ramistuni wrote:
Hi All, I am relatively new to postgres and need to upgrade postgres 
9.1.1 to 9.1.10. As I understood from the documentation that 
Upgrading to a minor release does not require a dump and restore; 
merely stop the database server, install the updated binaries, and 
restart the server Is there any step by step instructions available 
to do this kind of migration? We are currently running PostgreSQL 
9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 
20080704 (Red Hat 4.1.2-51), 64-bit We have 3 servers in production. 
Primary Standby (replication using streaming) Archive (uses slony) 
Thanks in advance 


were those postgres servers installed via yum?

if so, then simply doing...

yum update postgresql91

followed by

service postgresql-9.1 restart

on each machine should suffice.   I believe the recommended order is to 
upgrade the streaming slave first, then the master.  I don't know if 
Slony matters either way (I've never used it)


when doing minor upgrades, you should always read ALL the release notes 
for the versions greater than your existing and up to the new one you'll 
be installing.   Looking at 
http://www.postgresql.org/docs/9.1/static/release.html I see a couple of 
the updates require reindexing, also some modifications to citext stuff 
if you're using that.


--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
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] Upgrade from 9.1 to 9.10

2013-10-21 Thread David Johnston

ramistuni wrote
 We are currently running PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu,
 compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit 

This is packaging (thus Linux Distro) concern and not that of PostgreSQL
proper.  As I use Ubuntu and you appear to be using a Red Hat variant I
cannot help with specifics but assuming you (or someone) installed
PostgreSQL from a (Yum?) package that same mechanism would be used to
perform the upgrade.

0) Ensure you have working backups
1) Shut down PostgreSQL on all three servers
2) Run the relevant package upgrade command on each of the servers
3) Start PostgreSQL on all three servers.

Not positive about the order but I would think:
Shutdown Primary, Standby, Archive
Startup Archive, Standby, Primary

Suggest you practice in a development/staging/testing virtual environment
(snapshot-update-revert) before performing on production machines.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Upgrade-from-9-1-to-9-10-tp5775290p5775296.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Upgrade from 9.1 to 9.10

2013-10-21 Thread ramistuni

Thanks so much David and John for your quick responses.

These databases were recently transitioned to me from a team which are not
part of our organization any more. 

Please bare with my knowledge, but is there any way to find if the past
installations were done using YUM?

We have been given VMs from some third party and we are responsible for
maintaining and installing the database on them.

Thanks for your help.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Upgrade-from-9-1-to-9-10-tp5775290p5775311.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Upgrade from 9.1 to 9.10

2013-10-21 Thread John R Pierce


On 10/21/2013 12:00 PM, ramistuni wrote:

Please bare with my knowledge, but is there any way to find if the past
installations were done using YUM?


This is all standard redhat installation/administration stuff...

   # rpm -qf /usr/pgsql-9.2/bin/postgres
   postgresql92-server-9.2.5-1PGDG.rhel6.x86_64

(replace 9.2 with 9.1)... if its installed from the PGDG RPM like that, 
then it came from the postgres yum repository.


also verify they actually installed that repository, and didn't just 
manually jam the RPMs on there...


   # rpm -qf /etc/yum.repos.d/pgdg-9*
   pgdg-centos90-9.0-5.noarch
   pgdg-centos92-9.2-6.noarch

you should see the 91 version.


--
john r pierce  37N 122W
somewhere on the middle of the left coast

Re: [GENERAL] Upgrade from 9.1 to 9.10

2013-10-21 Thread John R Pierce


On 10/21/2013 12:15 PM, John R Pierce wrote:


# rpm -qf /etc/yum.repos.d/pgdg-9*
pgdg-centos90-9.0-5.noarch
pgdg-centos92-9.2-6.noarch

you should see the 91 version.



oh, to clarify something.the version of that pgdg-.noarch 
repository file is NOT directly related to the subversion of the minor 
release of postgres, just the version of the yum.repos.d repo file.




--
john r pierce  37N 122W
somewhere on the middle of the left coast

Re: [GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation

2013-09-18 Thread Kevin Grittner

Lonni J Friedman netll...@gmail.com wrote:

 top shows over 90% of the load is in sys space.  vmstat output
 seems to suggest that its CPU bound (or bouncing back  forth):

Can you run `perf top` during an episode and see what kernel
functions are using all that CPU?

This looks similar to cases I've seen of THP defrag going wild. 
Did the OS version or configuration change?  Did the PostgreSQL
memory settings (like shared_buffers) change?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] upgrade from 9.2.x to 9.3 causes significant performance degradation

2013-09-18 Thread Lonni J Friedman

On Wed, Sep 18, 2013 at 2:02 AM, Kevin Grittner kgri...@ymail.com wrote:
 Lonni J Friedman netll...@gmail.com wrote:

 top shows over 90% of the load is in sys space.  vmstat output
 seems to suggest that its CPU bound (or bouncing back  forth):

 Can you run `perf top` during an episode and see what kernel
 functions are using all that CPU?

Oddly, the problem went away on its own yesterday just after 4PM, and
performance has remained 'normal' since that time.  I changed
absolutely nothing.  If/when it returns, I'll certainly capture that
output.


 This looks similar to cases I've seen of THP defrag going wild.
 Did the OS version or configuration change?  Did the PostgreSQL
 memory settings (like shared_buffers) change?

Nothing changed other than the version of postgres.  I re-used the
same postgresql.conf that was in place when running 9.2.x.

Anyway, here are the current THP related settings on the server:
[root@cuda-db7 ~]# grep AnonHugePages /proc/meminfo
AnonHugePages:548864 kB
[root@cuda-db7 ~]# egrep 'trans|thp' /proc/vmstat
nr_anon_transparent_hugepages 272
thp_fault_alloc 129173889
thp_fault_fallback 17462551
thp_collapse_alloc 148437
thp_collapse_alloc_failed 15143
thp_split 242


-- 
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] upgrade from 9.2.x to 9.3 causes significant performance degradation

2013-09-18 Thread Lonni J Friedman

On Wed, Sep 18, 2013 at 2:02 AM, Kevin Grittner kgri...@ymail.com wrote:
 Lonni J Friedman netll...@gmail.com wrote:

 top shows over 90% of the load is in sys space.  vmstat output
 seems to suggest that its CPU bound (or bouncing back  forth):

 Can you run `perf top` during an episode and see what kernel
 functions are using all that CPU?

I take back what I said earlier.  While the master is currently back
to normal performance, the two hot standby slaves are still churning
something awful.

If I run 'perf top' on either slave, after a few seconds, these are
consistently the top three in the list:
 84.57%  [kernel]   [k] _spin_lock_irqsave
  6.21%  [unknown]  [.] 0x00659f60
  4.69%  [kernel]   [k] compaction_alloc


 This looks similar to cases I've seen of THP defrag going wild.
 Did the OS version or configuration change?  Did the PostgreSQL
 memory settings (like shared_buffers) change?

I think you're onto something here with respect to THP defrag going
wild.  I set /sys/kernel/mm/transparent_hugepage/defrag to 'never' and
immediately the load dropped on both slaves from over 5.00 to under
1.00.

So this raises the question, is this a kernel bug, or is there some
other solution to the problem?
Also, seems weird that the problem didn't happen until I switched from
9.2 to 9.3.  Is it possible this is somehow related to the change from
using SysV shared memory to using Posix shared memory and mmap for
memory management?


-- 
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] upgrade from 9.2.x to 9.3 causes significant performance degradation

2013-09-18 Thread Jeff Janes

On Wed, Sep 18, 2013 at 10:30 AM, Lonni J Friedman netll...@gmail.comwrote:


 
  This looks similar to cases I've seen of THP defrag going wild.
  Did the OS version or configuration change?  Did the PostgreSQL
  memory settings (like shared_buffers) change?

 I think you're onto something here with respect to THP defrag going
 wild.  I set /sys/kernel/mm/transparent_hugepage/defrag to 'never' and
 immediately the load dropped on both slaves from over 5.00 to under
 1.00.

 So this raises the question, is this a kernel bug, or is there some
 other solution to the problem?
 Also, seems weird that the problem didn't happen until I switched from
 9.2 to 9.3.  Is it possible this is somehow related to the change from
 using SysV shared memory to using Posix shared memory and mmap for
 memory management?


I would guess that it is probably a kernel bug which gets efficiently
exercised by the SysV to Posix change.

Cheers,

Jeff

[GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation

Greetings,
I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming
replication hot standby slaves) on RHEL6-x86_64.  Yesterday I upgraded
from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant
performance degradation.  PostgreSQL simply feels slower.  Nothing
other than the version of PostgreSQL changed yesterday.  I used
pg_upgrade to perform the upgrade, and ran the generated
analyze_new_cluster.sh immediately afterwards, which completed
successfully.

Prior to the upgrade, I'd generally expect a load average of less than
2.00 on the master, and less than 1.00 on each of the slaves.  Since
the upgrade, the load average on the master has been in double digits
(hitting 100.00 for a few minutes), and the slaves are consistently
above 5.00.

There are a few things that are jumping out at me as behaving
differently since the upgrade.  vmstat processes waiting for runtime
counts have increased dramatically.  Prior to the upgrade the process
count would be consistently less than 10, however since upgrading it
hovers between 40  60 at all times.  /proc/interrupts Local timer
interrupts has increased dramatically as well.  It used to hover
around 6000 and is now over 20k much of the time.  However, I'm
starting to suspect that they are both symptoms of the problem rather
than the cause.

At this point, I'm looking for guidance on how to debug this problem
more effectively.

thanks


-- 
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] upgrade from 9.2.x to 9.3 causes significant performance degradation

2013-09-17 Thread Eduardo Morras

On Tue, 17 Sep 2013 09:19:29 -0700
Lonni J Friedman netll...@gmail.com wrote:

 Greetings,
 I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming
 replication hot standby slaves) on RHEL6-x86_64.  Yesterday I upgraded
 from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant
 performance degradation.  PostgreSQL simply feels slower.  Nothing
 other than the version of PostgreSQL changed yesterday.  I used
 pg_upgrade to perform the upgrade, and ran the generated
 analyze_new_cluster.sh immediately afterwards, which completed
 successfully.
 
 Prior to the upgrade, I'd generally expect a load average of less than
 2.00 on the master, and less than 1.00 on each of the slaves.  Since
 the upgrade, the load average on the master has been in double digits
 (hitting 100.00 for a few minutes), and the slaves are consistently
 above 5.00.
 
 There are a few things that are jumping out at me as behaving
 differently since the upgrade.  vmstat processes waiting for runtime
 counts have increased dramatically.  Prior to the upgrade the process
 count would be consistently less than 10, however since upgrading it
 hovers between 40  60 at all times.  /proc/interrupts Local timer
 interrupts has increased dramatically as well.  It used to hover
 around 6000 and is now over 20k much of the time.  However, I'm
 starting to suspect that they are both symptoms of the problem rather
 than the cause.
 
 At this point, I'm looking for guidance on how to debug this problem
 more effectively.

Don't know what happens but:

a) Does analyze_new_cluster.sh include a reindex? If not, indexs are useless 
because analyze statistics says so.
b) Did you configure postgresql.conf on 9.3.0 for your server/load? Perhaps it 
has default install values.
c) What does logs say? 

 thanks
 
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general


---   ---
Eduardo Morras emorr...@yahoo.es


-- 
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] upgrade from 9.2.x to 9.3 causes significant performance degradation

On Tue, Sep 17, 2013 at 9:54 AM, Eduardo Morras emorr...@yahoo.es wrote:
 On Tue, 17 Sep 2013 09:19:29 -0700
 Lonni J Friedman netll...@gmail.com wrote:

 Greetings,
 I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming
 replication hot standby slaves) on RHEL6-x86_64.  Yesterday I upgraded
 from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant
 performance degradation.  PostgreSQL simply feels slower.  Nothing
 other than the version of PostgreSQL changed yesterday.  I used
 pg_upgrade to perform the upgrade, and ran the generated
 analyze_new_cluster.sh immediately afterwards, which completed
 successfully.

 Prior to the upgrade, I'd generally expect a load average of less than
 2.00 on the master, and less than 1.00 on each of the slaves.  Since
 the upgrade, the load average on the master has been in double digits
 (hitting 100.00 for a few minutes), and the slaves are consistently
 above 5.00.

 There are a few things that are jumping out at me as behaving
 differently since the upgrade.  vmstat processes waiting for runtime
 counts have increased dramatically.  Prior to the upgrade the process
 count would be consistently less than 10, however since upgrading it
 hovers between 40  60 at all times.  /proc/interrupts Local timer
 interrupts has increased dramatically as well.  It used to hover
 around 6000 and is now over 20k much of the time.  However, I'm
 starting to suspect that they are both symptoms of the problem rather
 than the cause.

 At this point, I'm looking for guidance on how to debug this problem
 more effectively.

 Don't know what happens but:

 a) Does analyze_new_cluster.sh include a reindex? If not, indexs are useless 
 because analyze statistics says so.

No, it doesn't include a reindex.  It merely invokes vacuumdb --all
--analyze-only with different values for default_statistics_target=1
-c vacuum_cost_delay=0.

According to the documentation for pg_upgrade, post-upgrade scripts to
rebuild tables and indexes will be generated automatically.  Nothing
was generated for this purpose, at least not in any obvious place.
The analyze_new_cluster.sh script is the only one that was
automatically generated as far as I can tell.

 b) Did you configure postgresql.conf on 9.3.0 for your server/load? Perhaps 
 it has default install values.

Yes, I'm using the same postgresql.conf as I was using when running
9.2.4.  Its definitely not running with default install values.

 c) What does logs say?

The postgres server logs look perfectly normal, minus a non-trivial
slower run time for most queries.  There's nothing unusual in any of
the OS level logs (/var/log/messages, etc) or dmesg.



Do you have any other suggestions?


-- 
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] upgrade from 9.2.x to 9.3 causes significant performance degradation

2013-09-17 Thread Jeff Janes

On Tue, Sep 17, 2013 at 11:22 AM, Lonni J Friedman netll...@gmail.comwrote:


  c) What does logs say?

 The postgres server logs look perfectly normal, minus a non-trivial
 slower run time for most queries.  There's nothing unusual in any of
 the OS level logs (/var/log/messages, etc) or dmesg.


Are you generally CPU limited or IO limited?

Grab one of those slower queries and do an explain (analyze, buffers) of
it.  Preferably with track_io_timing on.

Do you still have test/dev/QA/etc copy running on 9.2 for comparison?

Cheers,

Jeff

Re: [GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation

Thanks for your reply.  Comments/answers inline below

On Tue, Sep 17, 2013 at 11:28 AM, Jeff Janes jeff.ja...@gmail.com wrote:
 On Tue, Sep 17, 2013 at 11:22 AM, Lonni J Friedman netll...@gmail.com
 wrote:


  c) What does logs say?

 The postgres server logs look perfectly normal, minus a non-trivial
 slower run time for most queries.  There's nothing unusual in any of
 the OS level logs (/var/log/messages, etc) or dmesg.


 Are you generally CPU limited or IO limited?

top shows over 90% of the load is in sys space.  vmstat output seems
to suggest that its CPU bound (or bouncing back  forth):

procs ---memory-- ---swap-- -io --system-- -cpu-
 r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id wa st
 1  0  17308 852016 141104 12707419200101800
6  4 90  0  0
 0  0  17308 872316 141104 12707420000 0   988  940  564
1  0 99  0  0
 0  0  17308 884288 141104 12707420800 0  1921 1202 2132
1  0 99  0  0
 0  0  17308 898728 141104 12707420800 0 0 1064  577
1  0 99  0  0
 2  0  17308 914920 141104 12707422400 044  820  427
1  0 99  0  0
 0  0  17308 926524 141104 12707427200 048 1173  585
1  0 99  0  0
108  1  17308 753648 141104 12707422400 0   236 9825 3901
12  5 83  0  0
50  0  17308 723156 141104 12707440000 0   144 43481 9105
20 79  1  0  0
45  0  17308 722860 141104 12707441600 0 8 32969 1998
1 97  2  0  0
47  0  17308 738996 141104 12707441600 0 0 34099 1739
1 99  0  0  0
101  0  17308 770220 141104 12707448000 032 38550 5998
 7 93  0  0  0
101  0  17308 775732 141104 12707451200 0   156 33889 5809
 4 96  0  0  0
99  0  17308 791232 141104 12707454400 0 0 32385 4981
0 100  0  0  0
96  0  17308 803156 141104 12707454400 024 32413 4824
0 100  0  0  0
87  0  17308 811624 141104 12707454400 0 0 32438 4470
0 100  0  0  0
83  0  17308 815500 141104 12707454400 0 0 32489 4159
0 100  0  0  0
80  0  17308 826572 141104 12707455200 033 32582 3948
0 100  0  0  0
73  0  17308 853264 141108 12707455200 052 32833 3840
0 100  0  0  0
73  0  17308 882240 141108 12707456000 0 4 32820 3594
0 100  0  0  0
72  0  17308 892256 141108 12707456000 0 0 32368 3516
0 100  0  0  0
###

iostat consistently shows %util under 1.00 which also suggests that
disk IO is not the bottleneck:
#
iostat -dx /dev/sdb 5
Linux 2.6.32-358.6.2.el6.x86_64 (cuda-db7) 09/17/2013 _x86_64_
   (32 CPU)

Device: rrqm/s   wrqm/s r/s w/s   rsec/s   wsec/s
avgrq-sz avgqu-sz   await  svctm  %util
sdb   0.02 0.216.91   31.33   651.60  1121.85
46.38 0.092.25   0.08   0.31

Device: rrqm/s   wrqm/s r/s w/s   rsec/s   wsec/s
avgrq-sz avgqu-sz   await  svctm  %util
sdb   0.00 0.000.008.00 0.0093.00
11.62 0.000.28   0.20   0.16

Device: rrqm/s   wrqm/s r/s w/s   rsec/s   wsec/s
avgrq-sz avgqu-sz   await  svctm  %util
sdb   0.00 0.000.00   11.00 0.00   125.40
11.40 0.000.16   0.16   0.18

Device: rrqm/s   wrqm/s r/s w/s   rsec/s   wsec/s
avgrq-sz avgqu-sz   await  svctm  %util
sdb   0.00 0.000.00  105.00 0.00  3380.40
32.19 0.292.76   0.03   0.34

Device: rrqm/s   wrqm/s r/s w/s   rsec/s   wsec/s
avgrq-sz avgqu-sz   await  svctm  %util
sdb   0.00 0.000.00   14.80 0.00  2430.60
164.23 0.000.12   0.09   0.14

Device: rrqm/s   wrqm/s r/s w/s   rsec/s   wsec/s
avgrq-sz avgqu-sz   await  svctm  %util
sdb   0.00 1.200.00   41.60 0.00  1819.40
43.74 0.020.45   0.05   0.20

Device: rrqm/s   wrqm/s r/s w/s   rsec/s   wsec/s
avgrq-sz avgqu-sz   await  svctm  %util
sdb   0.00 0.000.002.80 0.0032.00
11.43 0.000.00   0.00   0.00
#

mpstat also shows a virtually 0 iowait, with a ton of sys (CPU) time:
#
mpstat 2 10
Linux 2.6.32-358.6.2.el6.x86_64 (cuda-db7) 09/17/2013 _x86_64_
   (32 CPU)

12:53:19 PM  CPU%usr   %nice%sys %iowait%irq   %soft
%steal  %guest   %idle
12:53:21 PM  all7.360.00   92.580.000.000.03
0.000.000.03
12:53:23 PM  all6.350.00   90.430.000.000.03
0.000.003.19
12:53:25 PM  all3.130.00   68.200.000.000.02
0.000.00   28.66
12:53:27 PM  all6.070.00   68.460.000.000.03
0.000.00   25.44
12:53:29 PM  all5.830.00   94.140.000.000.03
0.000.000.00
12:53:31 PM  all5.750.00   94.140.000.000.11
0.000.000.00
12:53:33 PM  all7.650.00   40.32

Re: [GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation

2013-09-17 Thread Andres Freund

Hi,

On 2013-09-17 09:19:29 -0700, Lonni J Friedman wrote:
 I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming
 replication hot standby slaves) on RHEL6-x86_64.  Yesterday I upgraded
 from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant
 performance degradation.  PostgreSQL simply feels slower.  Nothing
 other than the version of PostgreSQL changed yesterday.  I used
 pg_upgrade to perform the upgrade, and ran the generated
 analyze_new_cluster.sh immediately afterwards, which completed
 successfully.

Where did you get 9.3.0 from? Compiled it yourself? Any chance you
compile with --enable-cassert or somesuch?

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
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] upgrade from 9.2.x to 9.3 causes significant performance degradation