Why Use Postgres - Part 2

The bootstrap is not really hard when you've done it once, just jot it down somewhere.

In my experience most people stumble because the network security in postgres is pretty tight by default.

This is easily fixed and needs to be done only once.

First: Find your pg_hba.conf. It's in the database-directory, that's often linked to /etc/postgresql.

  # Backup the original
  $ cp pg_hba.conf pg_hba.conf_orig

  # Now replace it with our desired security settings
  $ cat >pg_hba.conf <<EOF
  # Require password auth from remote hosts
  # localhost and local socket are trusted
  host  all all 0.0.0.0/0 md5
  local all all trust
  host  all all 127.0.0.1/32 trust
  EOF

  # restart
  $ /etc/init.d/postgresql restart

From now on you can connect as any user from localhost without a password. Thus, we can now just go about our business.

  # connect as user postgres (the super-user)
  $ psql -U postgres
  postgres=# create database dummy;
  postgres=# create user bob with password 'pony';
  postgres=# grant all on database dummy to bob;
  postgres=# ^D

  # Now you have a database that user bob can use.
  # From remote he will have to use the password 'pony'.
  # From localhost no password needed because of our pg_hba settings above.
  $ psql -U bob dummy
  bob=> create table ...

  /etc/init.d/postgresql reload

What is your platform? If it is Debian then it is as simple as this to install PostgreSQL and set up a PostgreSQL root user with the same username as your UNIX username (this to avoid having to modify pg_hba and and also not having to specify user when connecting to PostgreSQL).

  sudo apt-get install postgresql
  sudo -u postgres createuser -s `whoami`

After this you an just create a database and connect to it with:

  createdb my_db
  psql my_db

     sudo -u postgres createuser -s `whoami`

     Ophelia ~ $ sudo -u postgres whoami
     postgres
     Ophelia ~ $ sudo -u postgres echo `whoami`
     rich

As for backup, pg_dump dbname > db.dump will do the dump, psql -f db.dump dbname will do the restore. There are, of course, far more complicated ways of doing it, including doing it in binary format, or data / schema only, or individual tables, but those are all a flag or two away.

Ok here is a guide I wrote for installing & setting up Postgres in Ubuntu. It's in Spanish but you will find your way around:

http://lobotuerto.com/blog/2009/07/20/como-instalar-postgres...

It covers instalation, setting up the postgres user (the _root_ you want), changing the authentication schema so you can use it for Rails dev (or something else), and finally how to install the Postgres gem for your Ruby.

Actually a bigger question is how are people migrating from their Mysql databases to postgresql. I'm quite surprised on the lack of a standard, production grade, defacto mysql2pg tool (and sql server as well).

pgdump to export as SQL, just pipe into psql to import.

Are there any reasons not to use postgres, as a replacement for mysql?

New addition?

listen and notify go back to at least 6.4 which came out 1998 ish http://www.postgresql.org/docs/6.4/static/sql-listen.html http://www.postgresql.org/docs/6.4/static/sql-notify.html

What might have changed is the implementation though. I seem to remember that some point in the past I was investigating listen/notify and the driver (libpq) was forcing you to handle LISTEN by polling (calling PQnotifies periodically) which doesn't really help compared to polling on your own.

This has not changed so far:

http://www.postgresql.org/docs/9.1/static/sql-listen.htm

states

> With the libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the function PQnotifies to find out whether any notification events have been received.

It might be possible for drivers working without libpq by talking the postgres protocol directly on the wire to get real asynchronous behavior, though I don't know anything about how this is being handled on the server right now (it might still be polling internally on the server end).

One of the bug fixes for Postgres95 Release 0.03 (Released 1995-07-21) is:

* the LISTEN/NOTIFY asynchronous notification mechanism now work

http://www.postgresql.org/docs/7.4/static/release-0-03.html

I'm not sure how much I like stuffing random functionality into the database... Looking at the docs: "and then must periodically call the function PQnotifies to find out whether any notification events have been received" - it's actually polling via an SQL connection - that means even more persistent connections to the DB, another timer in the application, more difficult connection sharing (two pools rather than one).

Without knowing much about how it works - why would I want to use this system rather than just ZMQ/RMQ/...?

LISTEN and NOTIFY are a great feature. We used them heavily in one system I worked on. As the project evolved though, we decided the best way to use them was to have clients to both our main message brokers and the postgres server, and translate between them. Largely this was to avoid overloading the pg server as a message broker also -- particularly because for our uses dbus was a much better alternative for local system message passing. Now that NOTIFYs officially have payload support, YMMV.

It's not exactly new, 7.4 was the oldest version I cared to check, but I think it goes back more :) http://www.postgresql.org/docs/7.4/static/sql-notify.html

A recent addition to NOTIFY is the ability for notifies to carry payloads (which makes them even more useful)

As the other posters have said, it has been around forever, but was rewritten to be much faster and support a payload in 9.0.

The official docs are fantastic. The newer PacktPub books are also pretty good http://www.packtpub.com/postgresql-90-high-performance/book and http://www.packtpub.com/postgresql-9-admin-cookbook

You can check the guide mentioned in the article: http://www.postgresguide.com/

I kind of hate this "tl;dr" culture. I can explain the reason why this is never going to change (though it may get better with index scans) but I expect you'll just see a wall of text and decide not to read it. But if you never read long answers, you have no right to expect solutions to your problems or to understand anything. So I hope you'll read this, it may help.

PostgreSQL and most modern, ACID-compliant RDBMSes use MVCC instead of locks. MVCC has a lot of upsides, namely, you almost never need table-level locking for writes, but it ensures you need to look at the whole table to do COUNT. That's because MVCC is basically multiple parallel universes for data. What you see depends critically on which transaction you're in when you look.

For example, suppose you have a process that needs to know the number of users in some user table. You start your transaction, and then another process starts a transaction that goes through and deletes certain users. While this is happening, you need the count. In MVCC, deletion doesn't delete, it just marks rows with the transaction ID range in which they're visible. The rows that are being deleted by the other process are just being marked dead as of that process's transaction ID, but your transaction ID is below that one, so they're not dead to you. Later on, when the youngest transaction alive is older than the oldest transaction that can see these rows, PostgreSQL will actually expunge the data (VACUUM does this).

Because of MVCC, there is more than one legitimate answer the question of COUNT; potentially, one for each transaction in progress. The answer is changing constantly. People tend not to think in these terms; we tend to think, well the table has so many rows in it, right? Why don't you just increment the count when you write one? It's not true, because with ACID compliance, uncommitted transactions should not have visible effects until they commit and transactions in progress should not see anything vary during their operation. So there really are multiple right answers at any given moment. The expectation that SELECT COUNT(*) will be fast is built on the assumption that the database has some sort of master count of what rows are in the table it can just glance at. But it doesn't, and to have one would require removing MVCC altogether and using locks instead.

If you mean, "have other major database vendors, such as MySQL, become slower in most of the same ways", then "yes, and for the same reasons ;P"; the one place PostgreSQL could be better on that is that it can't use a covering index currently (which would let it get the count of a filtered query only with a scan of the index, without needing to read and validate the table rows), but that is being addressed in 9.2.

If you want an approximate count you can do something like to get data for all your tables:

     SELECT S.nspname || '.' || relname, reltuples 
      FROM pg_class C,  pg_namespace S 
      WHERE C.relnamespace = S.oid 
      ORDER BY reltuples DESC

This is the estimate used by the query planner, so it's updated whenever a VACUUM occurs (not sure how often the autovacuum runs).