Penguin
Recent Changes
Blame: PostgreSQLNotes
EditPageHistoryDiffInfoLikePages
Annotated edit history of PostgreSQLNotes version 9, including all changes. View license author blame.
Rev Author # Line
2 AristotlePagaltzis 1 !! IDENT authentication failed for user
1 DrewBroadley 2
2 AristotlePagaltzis 3 [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.
1 DrewBroadley 4
2 AristotlePagaltzis 5 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.
1 DrewBroadley 6
2 AristotlePagaltzis 7 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>.
1 DrewBroadley 8
2 AristotlePagaltzis 9 !! Upgrading from 7.2 to 7.3
1 DrewBroadley 10
2 AristotlePagaltzis 11 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].
1 DrewBroadley 12
2 AristotlePagaltzis 13 Also, in [SQL]-land, LIMIT used to be fairly forgiving about its arguments. It would accept [MySQL] format without blinking. Now it's strict.
1 DrewBroadley 14
2 AristotlePagaltzis 15 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>.
1 DrewBroadley 16
3 AristotlePagaltzis 17 !! <tt>You should create PGDATA directory first</tt> in [Gentoo] after an emerge install
1 DrewBroadley 18
2 AristotlePagaltzis 19 If you get the following error:
1 DrewBroadley 20
2 AristotlePagaltzis 21 <verbatim>
22 # /etc/init.d/postgresql start
23 * directory not found: /var/lib/postgresql/data
24 * You should create PGDATA directory first.
25 </verbatim>
1 DrewBroadley 26
3 AristotlePagaltzis 27 then you need to issue <tt>ebuild /var/db/pkg/dev-db/postgresql-__$VERSION__/postgresql-__$VERSION__.ebuild config</tt>
4 AlastairPorter 28
5 AristotlePagaltzis 29 !! Connecting over [TCP/IP]
4 AlastairPorter 30
5 AristotlePagaltzis 31 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
4 AlastairPorter 32
5 AristotlePagaltzis 33 You will also need to edit your <tt>pg_hba.conf</tt> file to set access permissions.
8 MattBrown 34
35 !! Determining Table Primary Keys
36
37 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.
38
39 <verbatim>
40 CREATE VIEW relid_pkeys AS SELECT indrelid AS relid, attname AS column_name
41 FROM pg_index, pg_class, pg_attribute WHERE indisprimary='t' AND
42 indexrelid=pg_class.oid AND pg_attribute.attrelid=pg_class.relfilenode;
43
44 CREATE VIEW table_pkeys AS SELECT c.relname AS table_name, p.column_name FROM
45 relid_pkeys p, pg_class c WHERE p.relid=c.relfilenode AND c.relowner!=1 AND
46 c.relkind='r';
47 </verbatim>
48
49 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.
50
51 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.
9 AristotlePagaltzis 52
53 !! Tuning the [WAL]
54
55 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].
56
57 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].
58
59 Some additional configuration notes:
60
61 <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>:
62
63 These settings trade throughput for <tt>COMMIT</tt> latency.
64 The circumstances under which increasing <tt>commit_delay</tt> has most notable effect are when:
65
66 * There is no battery-backed write cache
67 * There are a lot of small transactions
68 * The [WAL] device has a high fsync(2) latency
69 * There are a large number of concurrent connections
70
71 GuyThornley's favourite settings for these parameters are:''''
72
73 <verbatim>
74 commit_delay = 20000
75 commit_siblings = 3
76 </verbatim>
77
78 <tt>[wal_buffers | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-WAL-BUFFERS]</tt>:
79
80 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.
81
82 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.
83
84 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.
85
86 <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>:
87
88 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.
89
90 Current reccomendations are to have checkpoints occur no more frequently than 5 minute intervals. Thus the settings
91
92 <verbatim>
93 checkpoint_timeout = 600
94 checkpoint_warning = 300
95 </verbatim>
96
97 seem reasonable.
98
99 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.
100
101 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>
102
103 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>

PHP Warning

lib/blame.php:177: Warning: Invalid argument supplied for foreach()