{"id":6602,"date":"2015-12-22T19:46:51","date_gmt":"2015-12-22T18:46:51","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/"},"modified":"2015-12-22T19:46:51","modified_gmt":"2015-12-22T18:46:51","slug":"how-fast-can-you-setup-a-standby-database-with-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/","title":{"rendered":"How fast can you setup a standby database with PostgreSQL?"},"content":{"rendered":"

While setting up a PostgreSQL infrastructure at customer today my colleague Pierre Sicot<\/a> and I discussed on how time consuming it is to setup standby databases if you compare different vendors. Lets make a challenge out of this. This is the baseline:<\/p>\n

    \n
  1. Install the product from scratch<\/li>\n
  2. Create a master database<\/li>\n
  3. Create a hot standby database (on the same host)<\/li>\n
  4. The standby database needs to be open read only<\/li>\n
  5. Create a table containing one record on the master<\/li>\n
  6. Verify that the table and the record is there on the standby<\/li>\n<\/ol>\n

    For this PostgreSQL demo I used the just released PostgreSQL 9.5 RC1<\/a>.<\/p>\n

    This is the script:<\/p>\n

    \npostgres@oel7:\/home\/postgres\/ [dummy] cat create_standby_from_scratch.sh \n#!\/bin\/bash\n\nBASE=$HOME\nSOURCE=\"${HOME}\/postgresql-9.5rc1.tar.bz2\"\nPGHHOME=\"${HOME}\/pg95rc\"\n\nMASTERDATADIR=\"${HOME}\/pgmaster\"\nSTANDBYDATADIR=\"${HOME}\/pgstandby\"\n\nPATH=${PHOME}\/bin:$PATH\nexport PATH\n\npg_ctl stop -D ${HOME}\/pgstandby -m fast\npg_ctl stop -D ${HOME}\/pgmaster -m fast\n\nrm -rf ${PGHHOME}\nrm -rf ${HOME}\/postgresql-9.5rc1}\nrm -rf ${MASTERDATADIR}\nrm -rf ${STANDBYDATADIR}\n\n# configure, compile and install\ncd ${HOME}\ntar -axf ${SOURCE}\ncd ${HOME}\/postgresql-9.5rc1\n.\/configure --prefix=${PGHHOME} \nmake world\nmake install\n\nPATH=${PHOME}\/bin:$PATH\nexport PATH\n\n# initialze the master postgresql cluster\ninitdb -D ${MASTERDATADIR}\necho \"listen_addresses = '*'\" > ${MASTERDATADIR}\/postgresql.conf\necho \"port=7777\" >> ${MASTERDATADIR}\/postgresql.conf\necho \"wal_level='hot_standby'\" >> ${MASTERDATADIR}\/postgresql.conf\necho \"max_replication_slots=3\" >> ${MASTERDATADIR}\/postgresql.conf\necho \"max_wal_senders=3\" >> ${MASTERDATADIR}\/postgresql.conf\npg_ctl start -D ${MASTERDATADIR}\nsleep 2\npsql -p 7777 -c \"select * from pg_create_physical_replication_slot('test');\" postgres\npg_ctl stop -D ${MASTERDATADIR} -m fast\n\n# prepare the primary\nmkdir ${STANDBYDATADIR}\nchmod 700 ${STANDBYDATADIR}\ncp -pr ${MASTERDATADIR}\/* ${STANDBYDATADIR}\/\necho \"standby_mode = 'on'\" > ${STANDBYDATADIR}\/recovery.conf\necho \"primary_slot_name = 'test' \" >> ${STANDBYDATADIR}\/recovery.conf \necho \"primary_conninfo = 'user=postgres port=7777 host=localhost'\" >> ${STANDBYDATADIR}\/recovery.conf\necho \"recovery_target_timeline = 'latest'\" >> ${STANDBYDATADIR}\/recovery.conf\necho \"port=7778\" >> ${STANDBYDATADIR}\/postgresql.conf\necho \"hot_standby='on'\" >> ${STANDBYDATADIR}\/postgresql.conf\necho \"host    replication     postgres        ::1\/128            trust\" >> ${MASTERDATADIR}\/pg_hba.conf\n\npg_ctl start -D ${MASTERDATADIR}\nsleep 2\npg_ctl start -D ${STANDBYDATADIR}\n\n## create a sample table on the master\npsql -p 7777 -c \"create table t1 ( a int );\" postgres\npsql -p 7777 -c \"insert into t1 values (1);\" postgres\nsleep 1\n# check the table on the standby\npsql -p 7778 -c \"select * from t1;\" postgres\n<\/pre>\n
    And this is the result (in a VirtualBox VM on my notebook, not a big server):<\/p>\n
    \npostgres@oel7:\/home\/postgres\/ [dummy] time .\/create_standby_from_scratch.sh\n...\nLOG:  autovacuum launcher started\nserver starting\nCREATE TABLE\nINSERT 0 1\nLOG:  database system was shut down at 2015-12-22 19:41:20 GMT\nLOG:  entering standby mode\nLOG:  consistent recovery state reached at 0\/1718740\nLOG:  record with zero length at 0\/1718740\nLOG:  database system is ready to accept read only connections\nLOG:  started streaming WAL from primary at 0\/1000000 on timeline 1\nLOG:  redo starts at 0\/1718740\n a \n---\n 1\n(1 row)\n...\nreal\t0m50.061s\nuser\t0m8.908s\nsys\t0m4.796s\n<\/pre>\n
    Less than a minute. The battle is open \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"
    While setting up a PostgreSQL infrastructure at customer today my colleague Pierre Sicot and I discussed on how time consuming it is to setup standby databases if you compare different vendors. Lets make a challenge out of this. This is the baseline: Install the product from scratch Create a master database Create a hot standby […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-6602","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nHow fast can you setup a standby database with PostgreSQL? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How fast can you setup a standby database with PostgreSQL?\" \/>\n<meta property=\"og:description\" content=\"While setting up a PostgreSQL infrastructure at customer today my colleague Pierre Sicot and I discussed on how time consuming it is to setup standby databases if you compare different vendors. Lets make a challenge out of this. This is the baseline: Install the product from scratch Create a master database Create a hot standby […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-22T18:46:51+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"How fast can you setup a standby database with PostgreSQL?\",\"datePublished\":\"2015-12-22T18:46:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/\"},\"wordCount\":139,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/\",\"name\":\"How fast can you setup a standby database with PostgreSQL? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-12-22T18:46:51+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How fast can you setup a standby database with PostgreSQL?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How fast can you setup a standby database with PostgreSQL? - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"How fast can you setup a standby database with PostgreSQL?","og_description":"While setting up a PostgreSQL infrastructure at customer today my colleague Pierre Sicot and I discussed on how time consuming it is to setup standby databases if you compare different vendors. Lets make a challenge out of this. This is the baseline: Install the product from scratch Create a master database Create a hot standby […]","og_url":"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2015-12-22T18:46:51+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"How fast can you setup a standby database with PostgreSQL?","datePublished":"2015-12-22T18:46:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/"},"wordCount":139,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/","name":"How fast can you setup a standby database with PostgreSQL? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-12-22T18:46:51+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/how-fast-can-you-setup-a-standby-database-with-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How fast can you setup a standby database with PostgreSQL?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6602","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=6602"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6602\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=6602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=6602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=6602"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=6602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}