{"id":1813,"date":"2010-07-12T20:17:16","date_gmt":"2010-07-13T04:17:16","guid":{"rendered":"http:\/\/www.chesnok.com\/daily\/?p=1813"},"modified":"2012-03-26T02:55:37","modified_gmt":"2012-03-26T10:55:37","slug":"qa-about-hot-standby","status":"publish","type":"post","link":"https:\/\/www.chesnok.com\/daily\/2010\/07\/12\/qa-about-hot-standby\/","title":{"rendered":"Q&A about Hot Standby"},"content":{"rendered":"

Updated!<\/strong>: See below.<\/p>\n

Here are some questions that came up from trying to use the current PostgreSQL hot standby<\/a> documentation: <\/p>\n

Q: If you set hot_standby = off<\/code> after having it on, what happens?
\n<\/strong>
\nA: This change requires a database restart on the hot standby (or replica) server. The database goes into “warm standby” mode, and you can no longer issue queries against it. You can change this right back by setting the parameter to ‘on’ and restarting again.<\/p>\n

Q: Can you use hot standby with only a single schema or database?
\n<\/strong>
\nA: No. Hot Standby is all-or-nothing for a particular PostgreSQL database cluster<\/a>. A cluster is made up of all the databases that live in a particular $PGDATA instance, and Hot Standby is currently not capable of distinguishing between changes occurring on different particular databases or schemas.<\/p>\n

Q: Is the process for setting up hot standby any different for empty databases vs. populated databases?
\n<\/strong>
\nA: No. The setup process is the same – you must create a base backup<\/a>.<\/p>\n

Q: How do I bring my hot standby out of standby mode?
\n<\/strong>
\nA: If you’re using something like the following in your recovery.conf file:
\n
\nrestore_command = 'cp xxxx'
\nstandby_mode = 'on'
\n<\/code><\/p>\n

Change: standby_mode = 'off'<\/code> and restart your hot standby postgresql instance.<\/p>\n

Q: Where did my recovery.conf<\/code> file go? (after your database came out of warm\/hot standby)
\n<\/strong>
\nA: PostgreSQL automatically changes the name of the file to recovery.done<\/code> when recovery completes. This helps prevent accidents.<\/p>\n

Q: What happens if my archive_timeout = 60<\/code> (which creates a 16mb file every minute) and I flood the database with so much activity that my standby falls behind?
\n<\/strong>
\nA: This is possible, and you may be interested in trying Streaming Replication<\/a>. However, for the majority of users, a delay in restoring data is acceptable (and possibly desirable). Eventually the standby server will catch up. You can monitor how delayed the server is using functions like txid_current_snapshot()<\/code>.<\/p>\n

Q: Are schema changes (like CREATE TABLE or ALTER TABLE) replicated to the standby?
\n<\/strong>
\nA: Yes! All changes to the database cluster are copied to the standby. This includes any DDL operations, new rows, the effects of autovacuum — any change to the data store on the master is copied to the standby.<\/p>\n","protected":false},"excerpt":{"rendered":"

Updated!: See below. Here are some questions that came up from trying to use the current PostgreSQL hot standby documentation: Q: If you set hot_standby = off after having it on, what happens? A: This change requires a database restart … Continue reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[97,9],"tags":[328,182,647,624,327,326],"class_list":["post-1813","post","type-post","status-publish","format-standard","hentry","category-postgres","category-postgresql","tag-9-0","tag-hot-standby","tag-postgres","tag-postgresql","tag-qa","tag-quickstart"],"_links":{"self":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/1813","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/comments?post=1813"}],"version-history":[{"count":10,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/1813\/revisions"}],"predecessor-version":[{"id":3984,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/1813\/revisions\/3984"}],"wp:attachment":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/media?parent=1813"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/categories?post=1813"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/tags?post=1813"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}