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 /etc/postgres/pg_hba.conf (Host Based Authentication) by changing ident sameuser with trust to let anyone connect to the database with any username without a password, or crypt 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 postgres user connects to every database and runs some optimisations.
The Unix SuperUser can change to the postgres user to run the shell commands createdb and createuser -- 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 psql template1 to connect via the interactive shell, and then do create database name; or create user name;.
Finally, if you are a Debian user, don't believe the instructions in the update README! psql -e < dumpfile will not restore your database... you need to do psql template1 -e dumpfile.
If you get the following error:
# /etc/init.d/postgresql start * directory not found: /var/lib/postgresql/data * You should create PGDATA directory first.
then you need to issue ebuild /var/db/pkg/dev-db/postgresql-$VERSION/postgresql-$VERSION.ebuild config
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 -i flag
You will also need to edit your pg_hba.conf file to set access permissions.
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.
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';
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.
Some configuration parameters for configuring WAL behaviour are available. They are all documented in the online PostgreSQL documentation. A good overview of some of the specific configuration parameters is available here.
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:
These settings trade throughput for COMMIT latency. The circumstances under which increasing commit_delay has most notable effect are when:
GuyThornley's favourite settings for these parameters are:
commit_delay = 20000 commit_siblings = 3
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 wal_buffers = 128 is a good starting point. The default setting of 8 is surely too small for most systems.
lib/main.php:944: Notice: PageInfo: Cannot find action page