Обсуждение: [ADMIN] postgresql9.4 aws - no pg_upgrade

Поиск
Список
Период
Сортировка

[ADMIN] postgresql9.4 aws - no pg_upgrade

От
bala jayaram
Дата:
i want to upgrade my current aws ec2 postgresql9.3 to AWS RDS 9.4 server. 
so i would like to use pg_upgrade feature to convert 9.3 to 9.4 within EC2 instance for using amazon DMS (database migration service) utility. But i came to know that there is no pg_upgrade binary available from amazon repositories. Taking pg_dump from EC2 instance to RDS will take longer time, as our DB size is 300GB. Is there a way with less downtime for migrating existing EC2 9.3 to RDS?

Thanks
Balaji Jayaraman

Re: [ADMIN] postgresql9.4 aws - no pg_upgrade

От
"Joshua D. Drake"
Дата:
On 10/14/2017 08:53 PM, bala jayaram wrote:
> i want to upgrade my current aws ec2 postgresql9.3 to AWS RDS 9.4 server.
> so i would like to use pg_upgrade feature to convert 9.3 to 9.4 within 
> EC2 instance for using amazon DMS (database migration service) utility. 
> But i came to know that there is no pg_upgrade binary available from 
> amazon repositories. Taking pg_dump from EC2 instance to RDS will take 
> longer time, as our DB size is 300GB. Is there a way with less downtime 
> for migrating existing EC2 9.3 to RDS?

Look into the -j option with pg_dump/pg_restore

JD


> 
> Thanks
> Balaji Jayaraman
> 


-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
*****     Unless otherwise stated, opinions are my own.   *****


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

Fwd: [ADMIN] postgresql9.4 aws - no pg_upgrade

От
bala jayaram
Дата:
Hi Team,


We tried in production, pg_upgrade works well. But running vacuumdb , resulted in huge spike in CPU, system halted. Is there a way to fasten or parallel vacuum solution for faster recovery after pg_upgrade.

Our database size is around 500GB, contains multiple databases, huge records. What is the minimum way to do a vacuuming after pg_upgrade? This is for migration from 9.3 to 9.4.


Thanks
Balaji Jayaraman  

On Tue, Oct 17, 2017 at 2:13 PM, bala jayaram <balajayaram22@gmail.com> wrote:
Thanks, i resolved by following other forums. seems we should not have tablespace inside data ( even symlink) as well. Once i moved the tables space to other location & updated the symlinks, pg_upgrade completed very fast within 4 mins. Thanks a lot for your support. 

-Thanks
Balaji Jayaraman

On Mon, Oct 16, 2017 at 4:54 PM, bala jayaram <balajayaram22@gmail.com> wrote:
I created CentoS, moved the data of 9.3 , completed the setup. later installed 9.4 , I could see pg_upgrade in that. 
We have two tables space, which are symlink to directory just before data directory
#ls -lrt
/data/pgsql93/<tb1>
/data/pgsql93/<tb2>
/data/pgsql93/data/ --> here is the 9.3 pgdata presents

Inside /data/pgsql93/data/pg_tblspc/ , two folders are symlined into above folders(tb1 & tb2)

While doing pg_upgrade with link option, Do i need to copy the table space (tb1 & tb2) to the new pgsql94 directories? or Create Folders structure similar to pgsql93 tablespace?

i got an error while doing pg_upgrade with link option
psql:pg_upgrade_dump_globals.sql:91: ERROR:  directory "/data/pgsql93/<tb1>/PG_9.4_201409291" already in use as a tablespace

How to resolve this? 

Thanks
Balaji Jayaraman




On Mon, Oct 16, 2017 at 11:42 AM, Joshua D. Drake <jd@commandprompt.com> wrote:
On 10/16/2017 06:13 AM, bala jayaram wrote:
I have installed postgres-contrib package as well. But pg_upgrade is not available. From this forum, https://forums.aws.amazon.com/thread.jspa?threadID=227761
amazon didnt provide pg_upgrade, thats the concern. Am going to try out with CentOS with existing DB Data, and use RDS import using migration service.


 That was going to be my next suggestion.

Good Luck!


JD
--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
*****     Unless otherwise stated, opinions are my own.   *****




Re: [ADMIN] postgresql9.4 aws - no pg_upgrade

От
Vasilis Ventirozos
Дата:

> On 2 Nov 2017, at 23:03, bala jayaram <balajayaram22@gmail.com> wrote:
>
> Hi Team,
>
>
> We tried in production, pg_upgrade works well. But running vacuumdb , resulted in huge spike in CPU, system halted.
Isthere a way to fasten or parallel vacuum solution for faster recovery after pg_upgrade. 
>
> Our database size is around 500GB, contains multiple databases, huge records. What is the minimum way to do a
vacuumingafter pg_upgrade? This is for migration from 9.3 to 9.4. 

All you need to do right after the upgrade is getting new statistics by running "analyze" or by doing something like
vacuumdb-a -v -z. 
That should take a while but it shouldn't "halt" anything. I believe that 9.4 doesn't have -j in vacuumdb, so you can
script
something that will will get all tables, split them and run each part in X number of psqls.
When you are done with the statistics then scheduling a vacuum would be a good idea. this can be done during any
convenient 
time or you can just split the work using a script.

Regards,
Vasilis Ventirozos

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

Re: [ADMIN] postgresql9.4 aws - no pg_upgrade

От
bala jayaram
Дата:
Thank you for the response, I ran vacuum db with analyze in stages, it took 15 hours to complete.  Also I noticed auto vacuum enabled for one of the  huge database which is running in parallel to vacuum db . Is that the reason for running 15 hours ? Because we cannot wait for 15 hours of outage. What is the best way to address this ?

And if we do pg_dump and restore to AWS RDS of 9.4 from 9.3 Linux native postgres, Analyze or vacuumdb is required ?  We observed pg_dump and restore with -j parallel option also took more than 6 hours total,

What is the best way for moving into 9.4 RDS from 9.3 Linux based instance in quicker way ? Please suggest. 


Thanks
Balaji jayaraman

On Nov 2, 2017 5:27 PM, "Vasilis Ventirozos" <v.ventirozos@gmail.com> wrote:


> On 2 Nov 2017, at 23:03, bala jayaram <balajayaram22@gmail.com> wrote:
>
> Hi Team,
>
>
> We tried in production, pg_upgrade works well. But running vacuumdb , resulted in huge spike in CPU, system halted. Is there a way to fasten or parallel vacuum solution for faster recovery after pg_upgrade.
>
> Our database size is around 500GB, contains multiple databases, huge records. What is the minimum way to do a vacuuming after pg_upgrade? This is for migration from 9.3 to 9.4.

All you need to do right after the upgrade is getting new statistics by running "analyze" or by doing something like vacuumdb -a -v -z.
That should take a while but it shouldn't "halt" anything. I believe that 9.4 doesn't have -j in vacuumdb, so you can script
something that will will get all tables, split them and run each part in X number of psqls.
When you are done with the statistics then scheduling a vacuum would be a good idea. this can be done during any convenient
time or you can just split the work using a script.

Regards,
Vasilis Ventirozos

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

Re: [ADMIN] postgresql9.4 aws - no pg_upgrade

От
Shreeyansh Dba
Дата:
Hi Balaji jayaraman,

Please find our response inline...



On Fri, Nov 3, 2017 at 8:11 AM, bala jayaram <balajayaram22@gmail.com> wrote:
Thank you for the response, I ran vacuum db with analyze in stages, it took 15 hours to complete.  Also I noticed auto vacuum enabled for one of the  huge database which is running in parallel to vacuum db . Is that the reason for running 15 hours ? Because we cannot wait for 15 hours of outage. What is the best way to address this ?


Though manual vacuum is progress, however autovacuum get precedence if it is kicked off and leaving manual vacuum behind.
to avoid time being disable autovacuum till the manual vacuum gets completed and later enable it.

And if we do pg_dump and restore to AWS RDS of 9.4 from 9.3 Linux native postgres, Analyze or vacuumdb is required ?  We observed pg_dump and restore with -j parallel option also took more than 6 hours total,


pg_dump & pg_restore are logical which does not require Vacuum/Analyze, however down time is required.

What is the best way for moving into 9.4 RDS from 9.3 Linux based instance in quicker way ? Please suggest. 


The best way is, due to higher DB size, you can go with slony option which also doesn't need vacuum/analyze having benefit of lower down time. 


Thanks
Balaji jayaraman

On Nov 2, 2017 5:27 PM, "Vasilis Ventirozos" <v.ventirozos@gmail.com> wrote:


> On 2 Nov 2017, at 23:03, bala jayaram <balajayaram22@gmail.com> wrote:
>
> Hi Team,
>
>
> We tried in production, pg_upgrade works well. But running vacuumdb , resulted in huge spike in CPU, system halted. Is there a way to fasten or parallel vacuum solution for faster recovery after pg_upgrade.
>
> Our database size is around 500GB, contains multiple databases, huge records. What is the minimum way to do a vacuuming after pg_upgrade? This is for migration from 9.3 to 9.4.

All you need to do right after the upgrade is getting new statistics by running "analyze" or by doing something like vacuumdb -a -v -z.
That should take a while but it shouldn't "halt" anything. I believe that 9.4 doesn't have -j in vacuumdb, so you can script
something that will will get all tables, split them and run each part in X number of psqls.
When you are done with the statistics then scheduling a vacuum would be a good idea. this can be done during any convenient
time or you can just split the work using a script.

Regards,
Vasilis Ventirozos

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

Re: [ADMIN] postgresql9.4 aws - no pg_upgrade

От
Keith
Дата:


On Thu, Nov 2, 2017 at 9:55 PM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Balaji jayaraman,

Please find our response inline...



On Fri, Nov 3, 2017 at 8:11 AM, bala jayaram <balajayaram22@gmail.com> wrote:
Thank you for the response, I ran vacuum db with analyze in stages, it took 15 hours to complete.  Also I noticed auto vacuum enabled for one of the  huge database which is running in parallel to vacuum db . Is that the reason for running 15 hours ? Because we cannot wait for 15 hours of outage. What is the best way to address this ?


Though manual vacuum is progress, however autovacuum get precedence if it is kicked off and leaving manual vacuum behind.
to avoid time being disable autovacuum till the manual vacuum gets completed and later enable it.

And if we do pg_dump and restore to AWS RDS of 9.4 from 9.3 Linux native postgres, Analyze or vacuumdb is required ?  We observed pg_dump and restore with -j parallel option also took more than 6 hours total,


pg_dump & pg_restore are logical which does not require Vacuum/Analyze, however down time is required.



This is not entirely true. After a pg_restore, a vacuum is not required, but an analyze most certainly is. And in a major version upgrade, this is not a logical situation where only a select few tables are being restored. The whole cluster is getting dumped/restored.





What is the best way for moving into 9.4 RDS from 9.3 Linux based instance in quicker way ? Please suggest. 


The best way is, due to higher DB size, you can go with slony option which also doesn't need vacuum/analyze having benefit of lower down time. 


Thanks
Balaji jayaraman


If the desire is to get into RDS, Slony will not help there. Also, Slony is non-trivial to setup in the first place and I don't recommend it unless you actually need a logical replication solution of some sort. Honestly, if these kinds of downtimes are not desirable, I would strongly advise against migrating to RDS. While RDS is useful if you don't have the resources to maintain a replication and backup infrastructure, what you lose in the ability to fine tune your system can be quite dramatic.

If you're already on EC2, you can use pg_upgrade to do an in-place upgrade within just a few minutes using the --link option. And that will be true for the foreseeable future if you keep your instance outside of a SaaS provided database like RDS. Just be aware that using the --link option you cannot go back if there are problems. So recommend having a replica in place that you can fail over to if the upgrade doesn't work. You're still required to do an analyze after a pg_upgrade, but you can usually bring your systems back online after the first stage of the multi-stage analyze process. If there are critical tables that you need full statistics for immediately, just run an manual analyze on them in addition to the multi-stage analyze of the entire cluster. This will bring your system up with a pretty minimal downtime. I've done this myself for a system over 1TB; pg_upgrade itself took less than 1 minute and the multistage analyze took about 45 minutes total, the first two stages only taking about 15 minutes. So, again, if you're seeing performance this bad as part of an upgrade process, I'd highly recommend looking into upgrading your infrastructure or making it so that you can use the --link option if that wasn't done.

Another solution, since you're in EC2, would be just a temporary increase in your EC2 instance resources (CPU, RAM and most importantly IOPS). This can be done one of two ways: 1) by using AWS's ability to dynamically change some instance metrics for just the upgrade duration or 2) by creating a streaming replica on a faster server, failing over to it, upgrading there, then failing back to the slower system.

I'd really recommend staying in EC2 over RDS unless you have a specific need for what RDS is providing, especially for a database that large.


Keith

Re: [ADMIN] postgresql9.4 aws - no pg_upgrade

От
Shreeyansh Dba
Дата:

On Fri, Nov 3, 2017 at 10:12 AM, Keith <keith@keithf4.com> wrote:


On Thu, Nov 2, 2017 at 9:55 PM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Balaji jayaraman,

Please find our response inline...



On Fri, Nov 3, 2017 at 8:11 AM, bala jayaram <balajayaram22@gmail.com> wrote:
Thank you for the response, I ran vacuum db with analyze in stages, it took 15 hours to complete.  Also I noticed auto vacuum enabled for one of the  huge database which is running in parallel to vacuum db . Is that the reason for running 15 hours ? Because we cannot wait for 15 hours of outage. What is the best way to address this ?


Though manual vacuum is progress, however autovacuum get precedence if it is kicked off and leaving manual vacuum behind.
to avoid time being disable autovacuum till the manual vacuum gets completed and later enable it.

And if we do pg_dump and restore to AWS RDS of 9.4 from 9.3 Linux native postgres, Analyze or vacuumdb is required ?  We observed pg_dump and restore with -j parallel option also took more than 6 hours total,


pg_dump & pg_restore are logical which does not require Vacuum/Analyze, however down time is required.



This is not entirely true. After a pg_restore, a vacuum is not required, but an analyze most certainly is. And in a major version upgrade, this is not a logical situation where only a select few tables are being restored. The whole cluster is getting dumped/restored.


During verification, pg_restore update all stats during data loading with allocation of required new disk pages and found matching  compared with reltuples from pg_class & count(*) operation after completion of pg_restore with no further requirement of analyze activity.





What is the best way for moving into 9.4 RDS from 9.3 Linux based instance in quicker way ? Please suggest. 


The best way is, due to higher DB size, you can go with slony option which also doesn't need vacuum/analyze having benefit of lower down time. 


Thanks
Balaji jayaraman


If the desire is to get into RDS, Slony will not help there. Also, Slony is non-trivial to setup in the first place and I don't recommend it unless you actually need a logical replication solution of some sort. Honestly, if these kinds of downtimes are not desirable, I would strongly advise against migrating to RDS. While RDS is useful if you don't have the resources to maintain a replication and backup infrastructure, what you lose in the ability to fine tune your system can be quite dramatic.

If you're already on EC2, you can use pg_upgrade to do an in-place upgrade within just a few minutes using the --link option. And that will be true for the foreseeable future if you keep your instance outside of a SaaS provided database like RDS. Just be aware that using the --link option you cannot go back if there are problems. So recommend having a replica in place that you can fail over to if the upgrade doesn't work. You're still required to do an analyze after a pg_upgrade, but you can usually bring your systems back online after the first stage of the multi-stage analyze process. If there are critical tables that you need full statistics for immediately, just run an manual analyze on them in addition to the multi-stage analyze of the entire cluster. This will bring your system up with a pretty minimal downtime. I've done this myself for a system over 1TB; pg_upgrade itself took less than 1 minute and the multistage analyze took about 45 minutes total, the first two stages only taking about 15 minutes. So, again, if you're seeing performance this bad as part of an upgrade process, I'd highly recommend looking into upgrading your infrastructure or making it so that you can use the --link option if that wasn't done.

Another solution, since you're in EC2, would be just a temporary increase in your EC2 instance resources (CPU, RAM and most importantly IOPS). This can be done one of two ways: 1) by using AWS's ability to dynamically change some instance metrics for just the upgrade duration or 2) by creating a streaming replica on a faster server, failing over to it, upgrading there, then failing back to the slower system.

I'd really recommend staying in EC2 over RDS unless you have a specific need for what RDS is providing, especially for a database that large.


Keith


Re: [ADMIN] postgresql9.4 aws - no pg_upgrade

От
Vasilis Ventirozos
Дата:


On 3 Nov 2017, at 04:41, bala jayaram <balajayaram22@gmail.com> wrote:

Thank you for the response, I ran vacuum db with analyze in stages, it took 15 hours to complete.  Also I noticed auto vacuum enabled for one of the  huge database which is running in parallel to vacuum db . Is that the reason for running 15 hours ? Because we cannot wait for 15 hours of outage. What is the best way to address this ?

Here's a script i wrote for parallel analyze , it should drop your analyze time significantly.
it is relatively tested and its not very smart but it should work. You can adjust the split files to have a more equal distribution of
workload if you want. Also needs adjustment for rds as its meant to run locally. Hope this helps a bit.

#!/bin/bash
#

if [ $# -ne 2 ];
then echo "Usage: `basename $0` <dbname> <jobs> "
exit 0
fi

my_psql=/opt/postgresql/pgsql/bin/psql
db=$1
jobs=5
port=5432

function get_list_of_objects {
echo $db
$my_psql -d $db -t -p $port -o /tmp/all_objects.sql -c "select 'Vacuum analyze ' || c.oid::regclass ||' /* manual vacuum-analyze */ ;' \
FROM pg_class c LEFT JOIN pg_class t \
ON c.reltoastrelid = t.oid  WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]) "
}

function split_to_jobs {
number_of_rows=`wc -l < /tmp/all_objects.sql`
number_per_file=`expr $number_of_rows / $jobs`
number_per_file_round_up=`expr $number_per_file + 1`
split -l $number_per_file /tmp/all_objects.sql /tmp/objects_
}

function fix_files {
for a in `ls -1 /tmp/objects_*` ;
do echo "set timezone TO 'America/New_York'; select '$a ended at : '||now();">> $a
done
}

function run_vacuum {
ls -l /tmp/objects_*
for a in `ls -1 /tmp/objects_*` ;
do $my_psql -q -X -f $a -d $db -p $port  2>&1 &
done
}

function cleanup {
rm /tmp/objects_* /tmp/all_objects.sql || true
}
cleanup
get_list_of_objects;
split_to_jobs;
fix_files
run_vacuum
#|grep -v "WARNING"


Regards,
Vasilis Ventirozos

Re: [ADMIN] postgresql9.4 aws - no pg_upgrade

От
Payal Singh
Дата:
But i came to know that there is no pg_upgrade binary available from amazon repositories. 

Just for future reference, you don't *have* to use a packaged version of pg_upgrade. You could compile postgres binaries from source in a temp location, use that pg_upgrade to upgrade your database, and then remove the compiled folder and use the packaged binaries to manage the new cluster. 

i want to upgrade my current aws ec2 postgresql9.3 to AWS RDS 9.4 server. 

Probably better to move to 9.6 at this point, or 10.1(which will be releasing soon)

Payal 

Payal Singh,
Database Administrator,
OmniTI Computer Consulting Inc.
Phone: 240.646.0770 x 253

On Sat, Oct 14, 2017 at 11:53 PM, bala jayaram <balajayaram22@gmail.com> wrote:
i want to upgrade my current aws ec2 postgresql9.3 to AWS RDS 9.4 server. 
so i would like to use pg_upgrade feature to convert 9.3 to 9.4 within EC2 instance for using amazon DMS (database migration service) utility. But i came to know that there is no pg_upgrade binary available from amazon repositories. Taking pg_dump from EC2 instance to RDS will take longer time, as our DB size is 300GB. Is there a way with less downtime for migrating existing EC2 9.3 to RDS?

Thanks
Balaji Jayaraman