View Source: PostgreSQLNotes - Waikato Linux Users Group

Edit PageHistory Diff Info LikePages
!! IDENT authentication failed for user

[PostgreSQL] under [Debian] by default uses [IDENT] authentication to authenticate a user connecting to the database.  In english this means that you can only connect to the database with the same username as your unix login.  This can be edited in <tt>/etc/postgres/pg_hba.conf</tt> (Host Based Authentication) by changing <tt>ident sameuser</tt> with <tt>trust</tt> to let anyone connect to the database with any username without a password, or <tt>crypt</tt> etc.  The file is well commented, refer to it for more details.

If you change [Debian]'s default setup, then you may get error messages every day from cron, as the <tt>postgres</tt> user connects to every database and runs some optimisations.

The [Unix] SuperUser can change to the <tt>postgres</tt> user to run the shell commands <tt>createdb</tt> and <tt>createuser</tt> -- if you create a database user with the same name as a [Unix] account, then that [Unix] account user can connect (when using ident sameuser). If you don't want to use the shell command, the postgres user could run <tt>psql template1</tt> to connect via the interactive shell, and then do <tt>create database ''name'';</tt> or <tt>create user ''name'';</tt>.

!! Upgrading from 7.2 to 7.3

Check your <tt>pg_hba.conf</tt> file. Previously "password" authentication covered all forms of stored password. Now it is strict. If you are using [MD5] passwords, it __must__ be [MD5].

Also, in [SQL]-land, LIMIT used to be fairly forgiving about its arguments.  It would accept [MySQL] format without blinking.  Now it's strict.

Finally, if you are a [Debian] user, don't believe the instructions in the update README! <tt>psql -e < ''dumpfile''</tt> will __not__ restore your database... you need to do <tt>psql template1 -e ''dumpfile''</tt>.

!! <tt>You should create PGDATA directory first</tt> in [Gentoo] after an emerge install

If you get the following error:

<verbatim>
# /etc/init.d/postgresql start
 * directory not found: /var/lib/postgresql/data
 * You should create PGDATA directory first.
</verbatim>

then you need to issue <tt>ebuild /var/db/pkg/dev-db/postgresql-__$VERSION__/postgresql-__$VERSION__.ebuild config</tt>

!! Connecting over [TCP/IP]

A default server will only listen for connections on a socket. If you want to connect to it via a host name then you need to start the server with the <tt>-i</tt> flag

You will also need to edit your <tt>pg_hba.conf</tt> file to set access permissions.

!! Determining Table Primary Keys

Within a procedure I was writing I needed a way to determine which fields of the table where the primary keys. The following views can help to provide this information.

<verbatim>
CREATE VIEW relid_pkeys AS SELECT indrelid AS relid, attname AS column_name
    FROM pg_index, pg_class, pg_attribute WHERE indisprimary='t' AND
    indexrelid=pg_class.oid AND pg_attribute.attrelid=pg_class.relfilenode;

CREATE VIEW table_pkeys AS SELECT c.relname AS table_name, p.column_name FROM
    relid_pkeys p, pg_class c WHERE p.relid=c.relfilenode AND c.relowner!=1 AND
    c.relkind='r';
</verbatim>

Now you can go ''SELECT column_name FROM table_pkeys WHERE table_name='foo''' and get back a list of the columns in the table ''foo'' that are primary keys. No records will be returned if ''foo'' does not have a primary key defined.

I added the restrictions on the ''relowner'' and ''relkind'' fields so that queries such as ''SELECT * FROM table_pkeys'' are a bit cleaner. The restrictions aim to filter the list so that it only contains user created tables. I chose the values based on trial and error, they work for me, but your milage may vary. If you are having problems simply remove those to restrictions.

!! Tuning the [WAL]

Some configuration parameters for configuring [WAL] behaviour are available. They are all documented in the online [PostgreSQL documentation | http://www.postgresql.org/docs/]. A good overview of some of the specific configuration parameters is available [here | http://www.postgresql.org/docs/8.0/static/wal-configuration.html].

Many people extol the virtue of placing the [WAL] on its own device, seperate from the main data files. Extensive PgBench testing leads to questioning that advice; the performance increase appears to be related to the increase in number of spindles more than the specific disk configuration. Compare the 4-disk RAID1+0 and dual RAID1 configurations. Those tests, however, were performed using a battery-backed write cache; it is entirely possible that without a write-cache the fsync(2) seek latency would significantly impact the main data file [I/O].

Some additional configuration notes:

<tt>[commit_delay | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-COMMIT-DELAY]</tt> %%% <tt>[commit_siblings | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-COMMIT-SIBLINGS]</tt>:

  These settings trade throughput for <tt>COMMIT</tt> latency.
  The circumstances under which increasing <tt>commit_delay</tt> has most notable effect are when:

  * There is no battery-backed write cache
  * There are a lot of small transactions
  * The [WAL] device has a high fsync(2) latency
  * There are a large number of concurrent connections

  GuyThornley's favourite settings for these parameters are:''''

    <verbatim>
    commit_delay = 20000
    commit_siblings = 3
    </verbatim>

<tt>[wal_buffers | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-WAL-BUFFERS]</tt>:

  This setting controls the upper bound on the size of write(2) requests to [WAL] segment files. Modern operating systems and disks work very well with writes in the range of 128kiB to 512kiB, often reaching peak [I/O] bandwidth with write(2) requests in the range.

  Before generating some new [WAL] activity, [PostgreSQL] writes out the [WAL] buffers if more than half are full. Given a default block size of 8 kiB, and the disk performance above, this suggests that <tt>wal_buffers = 128</tt> is a good starting point. The default setting of <tt>8</tt> is surely too small for most systems.

  Doing <tt>COMMIT</tt> both writes out all the used [WAL] buffers and issues an expensive fsync(2). Thus small transactions come with a heavy [I/O] penalty, and the [WAL] buffers are wasted. Using <tt>commit_delay</tt> may help significantly in this situation.

<tt>[checkpoint_segments | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-CHECKPOINT-SEGMENTS]</tt> %%% <tt>[checkpoint_timeout | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-CHECKPOINT-TIMEOUT]</tt> %%% <tt>[checkpoint_warning | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-CHECKPOINT-WARNING]</tt>:

  Good settings for these parameters are particularly hard to determine. On one hand, you want to allow [PostgreSQL] and the operating system perform write-caching of the main data files; on the other hand, you want to constrain crash-recovery times to reasonable levels.

  Current reccomendations are to have checkpoints occur no more frequently than 5 minute intervals. Thus the settings

    <verbatim>
    checkpoint_timeout = 600
    checkpoint_warning = 300
    </verbatim>

  seem reasonable.

  It is impossible to tune <tt>checkpoint_segments</tt> for worst-case [WAL] activity, such as a bulk load using <tt>COPY FROM STDIN</tt> [SQL] command. This might generate 30MiB/s or more of WAL activity. 30MiB/s for 5 minutes is 8.7GiB of WAL data! The best suggestion appears to be let the server run for a while, then study the timestamps on the WAL segment files in <tt>pg_xlog</tt> to calculate the [WAL] write rate.

  Once the [WAL] write rate is deduced, the number of log segments required for 10 minutes of [WAL] activity can be calculated as <i>checkpoint_segments = (wal_write_rate * 600) / 16</i>

  It is the author's experience that crash recovery from the [WAL] is a fairly slow operation. [I/O] rates in the region of 2MiB/s to 4MiB/s should be expected. The <tt>checkpoint_segments</tt> setting could be limited to the longest permissible recovery time. For example, to complete recovery within 5 minutes at 3.0MiB/sec, the maximum setting of <tt>checkpoint_segments</tt> is <i>(300 * 3) / 16 = 56</i>
2 pages link to PostgreSQLNotes:
Last edited on Friday, June 23, 2006 3:55:41 am by AristotlePagaltzis
Edit PageHistory Diff Info LikePages