![\"postgresql-logo\"](\"\/blog\/wp-ttn-blog\/uploads\/2015\/09\/postgresql-logo.png\"){kind=logo}
<\/p>\nPostgreSQL is another SQL. It is the second most popular Structured Query Language after MySQL. The steps discussed in the blog can be used for any kind of migration of PostgreSQL. From on-premise to cloud or on-premise to on-premise or cloud to on-premise.<\/p>\n
<\/p>\n
I had to migrate my PostgreSQL running on an EC2 server to RDS PostgreSQL for production environment with NO to minimal downtime & NO data loss which occurs when we usually make a dump, restore it and change the DB host. I read about Londiste – A tool that makes that happen easily. So, you can follow these steps to migrate running\u00a0PostgreSQL. in any environment. The thing to remember here is you don’t get a bash shell for AWS RDS. We\u00a0will achieve this by connecting the production PosgreSQL to the AWS RDS PostgreSQL which is production live and then starting the replication. Once the production DB is in sync with AWS RDS DB we make the DB host change in our application.<\/p>\n
All steps are basically for Ubuntu but just with minimal changes it will work on any other OS. The postgreSQL version is 9.1 in my case. The packages you would need to download are:<\/p>\n
[js]tar -xvf skytools-2.1.12.tar.gz<\/p>\n
cd \/usr\/local\/src\/skytools-2.1.12<\/p>\n
.\/configure –prefix=\/opt\/skytools-2.1.12 –with\u00a0pgconfig=\/usr\/lib\/postgresql\/9.1\/bin\/pg_config<\/p>\n
(\/usr\/lib\/postgresql\/9.1\/bin\/pg_config is the directory of postgreSQL config file and you can\u00a0locate the file by using\u00a0<strong>locate<\/strong> bash command)<\/p>\n
make<\/p>\n
make install [\/js]<\/p>\n
<\/p>\n
[js]tar -xvf psycopg2-2.4.2.tar.gz<\/p>\n
cd psycopg2-2.4.2<\/p>\n
python setup.py install –prefix=\/usr\/local<\/p>\n
python setup.py build_ext –pg-config \/usr\/lib\/postgresql\/9.1\/bin\/pg_config[\/js]<\/p>\n
<\/p>\n
Give the postgres<\/strong> user (the user which automatically gets created when you install postgreSQL)<\/p>\n [js]chown -R postgres:postgres \/opt\/skytools-2.1.12<\/p>\n chown -R postgres:postgres \/usr\/lib\/postgresql\/9.1\/ [\/js]<\/p>\n <\/p>\n Set the LD_LIBRARY_PATH & PYTHONPATH and start the two newly created clusters.<\/p>\n [js]export PYTHONPATH=\/opt\/skytools-2.1.12\/lib\/python2.6\/site-packages\/<\/p>\n export LD_LIBRARY_PATH=\/usr\/lib\/postgresql\/9.1\/lib\/:\/usr\/lib:\/opt\/perlbrew\/perls\/perl- <\/p>\n Londiste provides us with some default docs and files. So, for ease of use we will edit the example file itself and use it. Londiste.ini should look something like below:<\/p>\n In subscriber<\/strong>(destination) line: Enter same set of details for subscriber_db. Now, lets edit pgqadm.ini in the same directory:<\/p>\n One log & pid file will be in \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf Also, just before that go to \/opt\/skytools-2.1.12\/lib\/python2.6\/site-packages\/skytools and increase the timeout in the file psycopgwrapper.py\u00a0<\/strong>so that connection does not break if the host machine’s DB takes time to reply.<\/p>\n Install londiste.ini and install provider DB & subscriber DB( go to \/opt\/skytools-2.1.12\/bin\/) [js]londiste.py \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf\/londiste.ini provider install<\/p>\n londiste.py \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf\/londiste.ini subscriber install[\/js]<\/p>\n pgqadm.ini install and enable ticker:<\/strong><\/p>\n [js]pgqadm.py \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf\/pgqadm.ini install The daemon can be stopped<\/strong> using:<\/p>\n [js]londiste.py \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf\/londiste.ini replay\u00a0-s[\/js]<\/p>\n and started again using -d switch in place of -s<\/strong> Also, you will have to Repeat step 2 & step 4.<\/p>\n Add tables and start replication, for example in the following command table name family_nicknames is added:<\/p>\n [js]londiste.py \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf\/londiste.ini provider add londiste.py \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf\/londiste.ini subscriber add Once a table is added, the table will be copied and also all future changes made will automatically replicate in the RDS from the\u00a0master DB. To replicate all use:<\/p>\n [js]londiste.py \/etc\/skytools\/conf.ini provider add –all (adding all tables on provider for replication)<\/p>\n londiste.py \/etc\/skytools\/conf.ini subscriber add –all (adding all tables on subscriber for replication) [\/js]<\/p>\n <\/p>\n \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf then\u00a0tailf myfisrtpgreplication.log [js]psql –host abcd-postgres.xyz1234567.us-east-1.rds.amazonaws.com –username=dev[\/js]<\/p>\n To test, you can make a few changes to a table in the master or provider DB & then add the table for replication & then go\u00a0to the RDS PostgreSQL and check if the changes are replicated or not. Later once all the replication is complete (check logs) you just need to change the DB endpoint to AWS RDS endpoint<\/p>\n This is very useful when it comes to migration of on-premise applications with DB to AWS as a matter of fact any cloud platform. Hope this has been useful to you.<\/p>\n","protected":false},"excerpt":{"rendered":" PostgreSQL is another SQL. It is the second most popular Structured Query Language after MySQL. The steps discussed in the blog can be used for any kind of migration of PostgreSQL. From on-premise to cloud or on-premise to on-premise or cloud to on-premise. Use-case I had to migrate my PostgreSQL running on an EC2 server […]<\/p>\n","protected":false},"author":174,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":11},"categories":[1174,2348],"tags":[248,2547,2548,260,2545,986,1703,2549,942,1442,2546],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/26484"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/174"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=26484"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/26484\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=26484"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=26484"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=26484"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}4. Set paths:<\/span><\/h4>\n
\n5.14.2\/lib\/5.14.2\/x86_64-linux-gnu\/CORE: [\/js]<\/p>\n5. Edit the londiste.ini & pgqadm.ini\u00a0Files in the below directory cd \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf”<\/span><\/h4>\n
![\"Screenshot](\"\/blog\/wp-ttn-blog\/uploads\/2015\/09\/Screenshot-from-2015-10-09-194811.png\")
In provider<\/strong>(source) line: Enter provider_db<\/strong> details:
\ndbname<\/strong>=
\nport<\/strong>=5432 (For PostgreSQL)
\nhost<\/strong>= (hostname of EC2 PostgreSQL server)
\nI have not entered the username and password for this DB server since the credentials are in the PostgreSQL conf file.<\/p>\n
\n<\/strong>Then enter log file locations of your choice. Make sure postgres user has access to the directory.<\/p>\n![\"Screenshot](\"\/blog\/wp-ttn-blog\/uploads\/2015\/09\/Screenshot-from-2015-10-09-194958.png\")
\nIn this, enter details of source DB<\/strong>. I have kept the maint_delay_min<\/strong> to 5 minutes. Loop_delay<\/strong> should be least. It checks what has changed in the master in that interval of time and replicates onto the RDS Instance. \u00a0You can reduce it.<\/p>\n
\nfor Londiste.ini & the other set in \/var\/lib\/postgresql\/ \/log & \/pid folders. I\u00a0Have kept 2 sets of log & pid files different data in both.<\/p>\n6. Now, install the ini file and for that cd into \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf\/ directory<\/span><\/h4>\n
\nWe need londiste.ini file to validate the provider DB & the suscriber DB i.e. the destination DB, whether it is accessible or not.<\/p>\nNote: Error may occur if the current server you are working on has no access to the provider DB host or\u00a0subscriber host. So, first ensure connectivity using telnet from the server on host and port.<\/h5>\n
\npgqadm.py \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf\/pgqadm.ini ticker -d
\nlondiste.py \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf\/londiste.ini replay -d
\npgqadm.py \/opt\/skytools-2.1.12\/share\/doc\/skytools\/conf\/pgqadm.ini status[\/js]<\/p>\nNote: “-d” option is to run the londiste\/PgQ daemons in background.
\nStatus can be checked using the last command the status one.<\/h5>\n
\n(But if you kill the daemon process &\u00a0If this throws error In-case you stop\/start\/restart the server or process\u00a0you might need to delete the pid files before starting the ticker &\u00a0replay daemon again.\u00a0Delete the pid file in \/var\/lib\/postgresql\/pid\/myfisrtreplication.pid)<\/p>\n7. Initiating Replication<\/span><\/h4>\n
\nfamily_nicknames<\/p>\n
\nfamily_nicknames[\/js]<\/p>\n8. The replication starts. You can check the logs at:<\/span><\/h4>\n
\nand
\n\/var\/lib\/postgresql\/log\/myfisrtpgreplication.log<\/p>\n9. You can check the replication by logging in to RDS:<\/span><\/h4>\n