Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-10-12 Thread Patrick B 
2016-09-09 1:09 GMT+12:00 Scott Marlowe :

> On Tue, Sep 6, 2016 at 5:25 PM, John R Pierce  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.
Also, since Slony-I is asynchronous master-slave, all writes have to be
segregated to the master. Additionally, there is a noticeable lag (1-3
seconds) between the master and the slaves which may cause users to have an
inconsistent view of the data.



So, Slony won't be used by us.

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-08 Thread Scott Marlowe 
On Tue, Sep 6, 2016 at 5:25 PM, John R Pierce  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

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-07 Thread Patrick B 
>
>
> 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.
>
>
I'll be using DMS to migrate from an EC2 Instance to RDS instance. I won't
be using EC2 on production environment, so to migrate to a RDS I need to
use DMS...

I got some located servers at Rackspace, this means that I'm not using
Rackspace postgres database service... so If i'd use slony I'd have to
install/configure it on my own.

Thanks for the answers guys...  This migration must happen and it will, and
of course I'll keep u updated about how it went.



Patrick

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-07 Thread Jim Nasby 

On 9/6/16 10:53 PM, John R Pierce wrote:

1) setup new streaming slave at new provider using 9.2, wait for
replication to complete and satisfy yourself that the database is
complete and intact.
2) shut down master at old provider (and any other slaves), promote new
provider 9.2 to master.
3) pg_upgrade using --link option to 9.4 on new provider.   if this
fails, restart old master and start over at step 1
4) bring up applications on new database.  if they fail, restart old
master, and start over at step 1
5) bring up new slave(s) on new database.


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.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
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] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread John R Pierce 

On 9/6/2016 8:21 PM, Venkata B Nagothi wrote:


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


Thats quite huge. pg_upgrade would be a better choice and yes, 
downtime is needed. You need to have the database shutdown all through 
the upgrade process.


How long it will take depends on type of Disks you have. It is 
definitely a lot faster than pg_dump/pg_restore.


Just moving a copy of the 2.3TB data between service providers is going 
to take quite a long time.


ok, just a rough concept.

1) setup new streaming slave at new provider using 9.2, wait for 
replication to complete and satisfy yourself that the database is 
complete and intact.
2) shut down master at old provider (and any other slaves), promote new 
provider 9.2 to master.
3) pg_upgrade using --link option to 9.4 on new provider.   if this 
fails, restart old master and start over at step 1
4) bring up applications on new database.  if they fail, restart old 
master, and start over at step 1

5) bring up new slave(s) on new database.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread Venkata B Nagothi 
On Wed, Sep 7, 2016 at 10:43 AM, Patrick B  wrote:

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

Thats quite huge. pg_upgrade would be a better choice and yes, downtime is
needed. You need to have the database shutdown all through the upgrade
process.

How long it will take depends on type of Disks you have. It is definitely a
lot faster than pg_dump/pg_restore.

Regards,
Venkata B N

Fujitsu Australia

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread Patrick B 
>
>
> 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

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread John R Pierce 

On 9/6/2016 4:47 PM, Patrick B wrote:

1 - upgrade master server
2 - by performing #1 - the slaves would become unused (streaming 
replication wouldn't work)

3 - Setup new slave running Postgres 9.4
4 - dump the db to the new server



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.



--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread Patrick B 
>
>
> 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
>


damn!!! really? =( That's not good

will have to think about how will perform this

I think  what I would need to use is:


1 - upgrade master server
2 - by performing #1 - the slaves would become unused (streaming
replication wouldn't work)
3 - Setup new slave running Postgres 9.4
4 - dump the db to the new server


Do you guys agree?

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread Melvin Davidson 
On Tue, Sep 6, 2016 at 7:28 PM, Patrick B  wrote:

>
>
> 2016-09-07 11:25 GMT+12:00 John R Pierce :
>
>> 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.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread Patrick B 
2016-09-07 11:25 GMT+12:00 John R Pierce :

> 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

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread John R Pierce 

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.


--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread Melvin Davidson 
If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on
the slave.

On Tue, Sep 6, 2016 at 6:43 PM, John R Pierce  wrote:

> 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
>
>
If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on
the slave.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread John R Pierce 

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

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread Rich Shepard 

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?


Patrick,

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

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] Postgres UPGRADE from 9.2 to 9.4

2016-09-06 Thread Patrick B 
Hi guys,

I'll be migrating my Postgres 9.2 database from Rackspace to Amazon.

To do that work, I'll be using DMS at amazon... unfortunately DMS needs a
Postgres 9.4+ version at least (
http://docs.aws.amazon.com/pt_br/dms/latest/userguide/CHAP_Introduction.Sources.html)
- So that means that I'll have to upgrade my postgres version.

Questions:

1 - I'll have to upgrade the version on my Master server, right? I can't
upgrade only in a streaming replication slave server, right?

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?

Thanks guys,
Patrick