Thread: Postgres UPGRADE from 9.2 to 9.4

On 9/6/2016 3:27 PM, Rich Shepard wrote:

On Wed, 7 Sep 2016, Patrick B wrote:

2 - I've never done a Postgres upgrade before, can you give some guide
here? Is usually a easy thing? How long can it take? Is a downtime needed?

  I suggest the place to start is 'man pg_upgrade.' It is helpful.

pg_upgrade would require both 9.2 (with your existing database directory) and 9.4 be installed on the same server.

the easiest way to do this upgrade depends heavily on how large the database is.   'pg_dumpall -h oldserver | psql -h newserver'  might be simplest for modest data sizes, but way too slow for larger data sets.

-- 
john r pierce, recycling bits in santa cruz

On 9/6/2016 4:20 PM, Melvin Davidson wrote:

If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on the slave.

does rackspace support slony?  how about amazon dms ?

slony requires configuring replication on each table.  if the database has a large complex schema this could take considerable setup effort.

2016-09-07 11:25 GMT+12:00 John R Pierce <pierce@hogranch.com>:

On 9/6/2016 4:20 PM, Melvin Davidson wrote:

If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on the slave.

does rackspace support slony?  how about amazon dms ?

slony requires configuring replication on each table.  if the database has a large complex schema this could take considerable setup effort.

I'm not using slony.... I'm using the default streaming replication postgres 9.2 tool.

I'm setting up a postgres 9.2 master and a 9.4 streaming replication slave atm...

will dump the DB on the 9.2 and restore it into the 9.4 to see if that can be done.

I don't intend to upgrade both master and slaves on my current production scenario just to be able to use DMS

Patrick 

John,

Yes, Rackspace supports slony. I used to work there and they use slony on their core system.

Patrick,

You cannot stream directly from 9.2 to 9.4. However, you can just create a separate 9.4 cluster (on a separate port).

Then pg_dump from 9.2 in plain format and it will load on 9.4

how large is the full database cluster?

if its only a few GB or whatever, and not grossly complex, pg_dumpall -h oldhost | psql -h newhost, is the simplest way to move a complete set of databases from an old server to a new. if there are large tables with millions of rows indexed, make sure you set maintenance_work_mem on the new server to 2GB before running this.

how large is the full database cluster?

if its only a few GB or whatever, and not grossly complex, pg_dumpall -h oldhost | psql -h newhost, is the simplest way to move a complete set of databases from an old server to a new. if there are large tables with millions of rows indexed, make sure you set maintenance_work_mem on the new server to 2GB before running this.

it's a full copy from the production, so it's 2.3TB 

Or if you want, upgrade on your existing provider first, then setup streaming replication. But no matter what, pg_upgrade will require some form of downtime.

You could also use either Slony or londiste to directly migrate from Rackspace to an EC2 instance on 9.5; no need for DMS at all. That has the added benefit of allowing you to switch to a new version with virtually zero downtime (as in seconds if you script it) and allowing you to maintain the old version (along with any new data changes) in case you need to fail back (without data loss).

Keep in mind that in this case you're really only using DMS as a form of replication, so you might be better off just sticking with Postgres tools.

On Tue, Sep 6, 2016 at 5:25 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 9/6/2016 4:20 PM, Melvin Davidson wrote:
>>
>> If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on
>> the slave.
>
>
> does rackspace support slony?  how about amazon dms ?
>
> slony requires configuring replication on each table.  if the database has a
> large complex schema this could take considerable setup effort.
>

Not really. As of slony 2.2 you can use a regular expression to add
tables or sequences.

i.e.:

SET ADD TABLE (
    SET ID=1,
    TABLES='public\\.*'
);

repeat for sequences.  Two commands. I don't consider that considerable effort.
--
To understand recursion, one must first understand recursion.

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

https://wiki.postgresql.org/wiki/Slony

The main drawback to Slony-I even as a replication system is the complexity of its setup and administration. The design of the system, with the database itself being used for queueing row updates, also significantly increases the amount of data writing and I/O done by the DBMS.