Tag Archives: postgresql

postgres, postgresql , , , , ,

Q&A about Hot Standby

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

Q: Can you use hot standby with only a single schema or database?

A: No. Hot Standby is all-or-nothing for a particular PostgreSQL database cluster. 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.

Q: Is the process for setting up hot standby any different for empty databases vs. populated databases?

A: No. The setup process is the same – you must create a base backup.

Q: How do I bring my hot standby out of standby mode?

A: If you’re using something like the following in your recovery.conf file:

restore_command = 'cp xxxx'
standby_mode = 'on'

Change: standby_mode = 'off' and restart your hot standby postgresql instance.

Q: Where did my recovery.conf file go? (after your database came out of warm/hot standby)

A: PostgreSQL automatically changes the name of the file to recovery.done when recovery completes. This helps prevent accidents.

Q: What happens if my archive_timeout = 60 (which creates a 16mb file every minute) and I flood the database with so much activity that my standby falls behind?

A: This is possible, and you may be interested in trying Streaming Replication. 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().

Q: Are schema changes (like CREATE TABLE or ALTER TABLE) replicated to the standby?

A: 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.

postgres, postgresql , , , , , , 2 Comments

Quick start on Hot Standby

Updated.

We could have some better end-user documentation around creating a warm or hot standby system for basic postgresql replication.

To this end, I created a Quick Start doc on the wiki, but it could use more help. Maybe we should create some setup recipes for common situations?

Also – I wrote the following script during a hot standby bugbash PDXPUG had today:


#!/bin/sh

BINPATH=/usr/local/pg90/bin
CP=/bin/cp
PGCTL=${BINPATH}/pg_ctl
PSQL=${BINPATH}/psql
INITDB=${BINPATH}/initdb

sudo mkdir -p /var/tmp/archive
sudo chown ${USER} /var/tmp/archive

${INITDB} hotstandby1

echo 'wal_level = hot_standby' >> hotstandby1/postgresql.conf
echo 'archive_mode = on' >> hotstandby1/postgresql.conf
echo "archive_command = 'cp %p /var/tmp/archive/%f'" >> hotstandby1/postgresql.conf
echo "archive_timeout = 60" >> hotstandby1/postgresql.conf
echo "port = 6543" >> hotstandby1/postgresql.conf

${PGCTL} -D hotstandby1 start -l hotstandby1.log
sleep 5

${PSQL} -p 6543 postgres -c "select pg_start_backup('backup')"
${CP} -pR hotstandby1/ hotstandby2
${PSQL} -p 6543 postgres -c "select pg_stop_backup()"
rm hotstandby2/postmaster.pid
rm hotstandby2/pg_xlog/*

echo 'hot_standby = on' >> hotstandby2/postgresql.conf
echo 'port = 6544' >> hotstandby2/postgresql.conf
echo "standby_mode = 'on'" >> hotstandby2/recovery.conf
echo "restore_command = 'cp -i /var/tmp/archive/%f %p'" >> hotstandby2/recovery.conf

${PGCTL} -D hotstandby2 start -l hotstandby2.log

* Added port specification in case you’ve already got postgres running. Added a BINPATH for custom install directories.

postgres, postgresql , , ,

PgCon 2010 – PL/Parrot, Simulated Annealing, Exclusion Constraints, Postgres-XC

PgCon this year was full of bold ideas, delivered in the quiet manner typical of the Postgres community. Talks by Jonathan Leto, Jan Urbanski and Jeff Davis all presented new features and ideas that show there is so much room yet in Postgres as a project to contribute, and innovate. I was also delighted to see Postgres-XC (touted as a “Postgres RAC”) release code, and give a great presentation on the high-level details.

Jonathan Leto presented work on PL/Parrot, along with David Fetter. Parrot is a dynamic language virtual machine, allowing implementation of multiple dynamic languages which can then share classes (from the docs: “In theory, you will be able to write a class in Perl, subclass it in Python and then instantiate and use that subclass in a Tcl program.”). The project is to embed Parrot in PostgreSQL, and eventually, implement dynamic languages inside the virtual machine. Advantages to doing this are that it will make implementing new dynamic languages in Postgres much easier, because the language implementers won’t have to learn the PL interface. Another useful feature in PL/Parrot is the implementation of a security opcode in Parrot which essentially controls access to open(), a key to implementing a secure procedural language in Postgres. (I’m sure Jonathan will correct me if I didn’t describe this properly :D)

Jan Urbanski gave a talk on join ordering via Simulated Annealing, called Replacing GEQO. The approach was pretty interesting, involved math that required me to scratch my head a bit, and the initial performance improvements for many-join queries made it seem appealing. The original -hackers posting from Jan, includes a few hairy queries from Andres Freund which confound the GEQO referenced later in the thread. Jan’s posted the code, and I’m looking forward to seeing how it develops this year.

Jeff Davis presented exclusion constraints, which are part of 9.0. He is continuing his work on temporal data types with a clever and very useful generalization of UNIQUE. UNIQUE constrains equality, while exclusion constraints allow other operators (in the most cited example, Jeff demonstrates “overlaps” in the PERIOD datatype).

Postgres-XC was officially presented and released. For efforts in Postgres clustering, releasing the code is a huge step forward toward mainstreaming work in the community on clustering. This release solidifies community work that started last year, with NTT and the support of the Japanese PostgreSQL User Group in having a clustering summit back in November 2009.

I was disappointed to miss a few talks (like hypothetical indexes, pg_statsinfo, CB’s pgMQ) but looking forward to hearing the recordings as they are published!

postgres, postgresql , ,

Lightning talks from PgCon 2010

Thanks again to all the folks that volunteered to give five minute talks during the Lightning talks session at PgCon!

Our lightning talks this year were:

  • PostgreSQL Developer Meeting in Five Minutes – Bruce Momjian
  • Slony 1 => 2.0 – Steve Singer
  • PostgreSQL and Twisted – Jan Urbanski
  • The FlyMine Project – Matthew Wakeling
  • Enhanced Tools for PostgreSQL – Tomonari Katsumata
  • Servoy – Christophe Pettus
  • Tail_n_mail – Greg Sabino Mullane
  • GSOC – Robert Treat
  • Pg Staging – Dimitri Fontaine
  • Serializable Transaction Isolation – Kevin Grittner
  • 10 ways to wreck your database – Josh Berkus

All presentations are downloadable from: http://wiki.postgresql.org/wiki/PgCon_2010_Lightning_talks

Image Copyright © 2010 Richard Guy Briggs from here and used by permission.

community, postgres, postgresql , , , ,

Need reviewers – preparing for the first commitfest for 9.1

Now is the perfect time to get involved in Postgres development!

Starting June 15, we’re going to have a “reviewfest”, as a prelude to the first commitfest in July. We’ve already got 28 patches in the queue, and all need reviewers.

Think you’re not qualified to review patches? Think again!

From the Reviewing a Patch documentation:

If you can apply a patch and you can use the new feature, you’re already qualified to start reviewing it.

We will formally kick off a reviewfest on June 15.

We will assign reviewers and get all the patches that are queued up for 9.1 reviewed while the final touches are being applied to the 9.0 release. Have a look at Review A Patch guidelines for information about how to conduct your review. We also have a mailing list to track and recruit reviewers – pgsql-rrreviewers. (The extra R’s are for ’round-robin’)

Please subscribe to the list, and post if there is a particular patch you are interested in reviewing!

postgres, postgresql , , , ,

PgCon Day 1: Developer meeting

Jan, talking about DDL triggers

I’m back from the developer meeting where we blasted through eight hours of presentations and discussions from key members of the PostgreSQL developer community.

One great outcome from the meeting is that WE ARE MOVING TO GIT.

There’s lots of other great stuff on that wiki notes – like a list of possible features for 9.1, plans for DDL triggers, and a continued discussion of snapshot cloning.

There was also much approval voiced for the alpha process that Peter Eisentraut championed last year.

I volunteered to steer our next “reviewfest” – kicking off June 15! And I’m trying to rope another developer into the next commitfest, which we anticipate starting on July 15. The plan is to stick with our development schedule from last year.

And I’m working with Josh Berkus to put together an open issues list for 9.0. I think I went a bit overboard, and many of the things I listed are probably resolved – hopefully committers will make quick work of the list, and we’ll be speeding along toward releasing 9.0 in June!

I missed out on the group picture, so I look forward to someone’s photo editing efforts in the near future. 🙂

community, conference, postgres, postgresql , , ,

GSoC Mentor Summit and the new mentor’s manual

4040234286_8cfb2f4708

I’ve been in San Jose since Wednesday, working on a book and preparing for today’s Google Summer of Code Mentor’s Summit. We’re here at Google’s campus, setting up the schedule and meeting new and old friends.

A group of us – me, Jen, Alex, Bart, Jonathan, Leslie and Olly – worked with Adam Hyde from Flossmanuals.net to create a new GSoC mentoring guide. We “book sprinted”, writing the entire manual in two days. Leslie was nice enough to produce printed copies for attendees, and the whole thing is available online at: http://en.flossmanuals.net/GSoCMentoringGuide. Flossmanuals.net is pretty cool — you can create epub books, PDFs and beautiful looking printed books quite easily.

I was happy to reference the patch review process from PostgreSQL in the ‘upstream integration‘ chapter.

We’d love comments, feedback and contributions to the manual!

postgres, postgresql , , , , 2 Comments

Snow Leopard and PostgreSQL: installation help links

snow_leopard_yvonne_n_1968

A few reports of issues have been raised on the mailing lists around upgrading to Snow Leopard. There have been some good tutorials and hints posted on blogs that aren’t in the planet.postgresql.org roll, so here are a few things that might help you out:

Photo courtesy of yvonne_n_1968, under a Creative Commons license

postgres, postgresql , , , 2 Comments

Offline community, PUGs updates

Just before heading off to PgCon, I wrote about offline community and how it has positively impacted the tech community in Portland, OR. Specifically, I talked about the factors I thought encouraged women to participate.

My own experience with Postgres has been incredibly positive and welcoming. I always wish that I had more time to contribute.

I did find a little time this weekend to upgrade the PostgreSQL User Group site to the latest supported version of Drupal. We’re still on version 5.x, and hopefully I’ll be able to upgrade that to version 6.x soon. We’ve had a few problems with spammers, but I added a CAPTCHA that I hope isn’t too annoying for everyone.

If you have ideas for how to display the information on the PUGs site in a better way, please get in touch. I have a couple things I’d like to add soon – like a map of locations, and a better preview of recently posted articles.

postgres, postgresql , , , , 1 Comment

An opportunity for Postgres

I wrote up my thoughts on the opportunities for Postgres in light of the Oracle/Sun merger, and the response from our communities.

An excerpt:

As a developer and a sysadmin, my enthusiasm for Postgres comes directly from the people that work on the code. The love of their craft – developing beautiful, purpose-built code – is reflected in the product, the mailing lists and the individuals who make up our community.

When someone asks me why I choose Postgres, I have to first answer that it is because of the people I know who are involved in the project. I trust them, and believe that they make the best technology decisions when it comes to the core of the code.

I believe that there’s room for improvement in extending Postgres’ reach, and speaking to people who don’t already believe the same things that we believe: that conforming to the SQL standard is fundamentally a useful and important goal, that vertical scaling is an important design objective, and that consistency is just as important to excellent user experience as are verbose command names and syntactic sugar extensions.

Let me know what you think!