Created Thursday 2/12/2004
This document describes configuring and using the Postgres rdbms that ships with Fedora Core.
The postgres postmaster instance is typically run as a special OS user, named postgres by convention. Create the postgres user and a group, called either postgres or dba. The user is used to run the postgres server instance and will be the owner of the postgres system cataglog (files). In most unix systems, creating a user and group can be done with:
bash # useradd bash # groupadd
Check that the postgres user and group exist. This example uses the local file database /usr/passwd and /etc/group. Consult the appropriate documentation of NIS+ or LDAP are used.
bash # cat /etc/passwd | grep '^postgres:' bash # cat /etc/group | grep '^postgres:'
Postgres stores the database system tables in a directory structure, which it knows about via PGDATA (see #3). Create a directory owned by the postgres OS user, like this:
bash# mkdir -p /u01/postgres/database bash# chown -R postgres:postgres /u01/postgres
Postgres requires that the PGDATA environment variable point to the location of the database filesets. This is must the same as Oracle's ORACLE_HOME and ORACLE_SID variables.
Set the environment variable PGDATA to point to the directory created in #2 above. E.g., if the postgres OS user has a shell of bash then add export PGDATA=/u01/postgres/database to the ~/.bash_profile. E.g.,
postgres@bash $ echo export PGDATA=/u01/postgres/database >> $HOME/.bash_profile
Creating a Database System is not the same as creating a Database Instance. The Database System prepares the Postgres environment. The preparation process adds the system catalog and a default database system configuration (which includes authorization) into the PGDATA directory. This task must be done as the postgres user:
postgres@bash $ initdb -D /u01/postgres/database/
The above initdb process creates the following files and directories in the PGDATA location:
postgres@bash $ ls -laF $PGDATA drwx------ 10 postgres postgres 4096 2007-06-16 01:50 ./ drwxr-xr-x 3 postgres postgres 4096 2007-06-16 01:50 ../ drwx------ 5 postgres postgres 4096 2007-06-16 01:50 base/ drwx------ 2 postgres postgres 4096 2007-06-16 01:50 global/ drwx------ 2 postgres postgres 4096 2007-06-16 01:50 pg_clog/ -rw------- 1 postgres postgres 3414 2007-06-16 01:50 pg_hba.conf -rw------- 1 postgres postgres 1460 2007-06-16 01:50 pg_ident.conf drwx------ 4 postgres postgres 4096 2007-06-16 01:50 pg_multixact/ drwx------ 2 postgres postgres 4096 2007-06-16 01:50 pg_subtrans/ drwx------ 2 postgres postgres 4096 2007-06-16 01:50 pg_tblspc/ drwx------ 2 postgres postgres 4096 2007-06-16 01:50 pg_twophase/ -rw------- 1 postgres postgres 4 2007-06-16 01:50 PG_VERSION drwx------ 3 postgres postgres 4096 2007-06-16 01:50 pg_xlog/ -rw------- 1 postgres postgres 15336 2007-06-16 01:50 postgresql.conf
The Postgres Server instance can be started now that the database system is created. The server instance can be started with either:
More typically the Postgres Server instance postmaster is started from the init.d script /etc/init.d/postgresql. If the postgres Database System is not installed to the standard location (which is /var/lib/pgsql) then the init environment must be told where to find the Database System data (i.e., where PGDATA is). In Fedora Core, this is most simply done by setting the PGDATA environment variable in the sysconfig system settings. Edit the file /etc/sysconfig/pgsql/postgresql and add the following entries:
▨ Note: The configuration file and the init script have the same name. E.g., postgresql in /etc/init.d and /etc/sysconfig/pgsql
PGDATA=/u01/postgres/database PGPORT=5432 PGLOG=/var/lib/pgsql/pgstartup.log PG_INITDB=0
Note that the postgres user directory (which has the .bash_profile and other related os environment) is located in the standard postgres /var/lib/pgsql directory. However, the Postgres Database System is this example is located in /u01/postgres/database. The permissions on the Database system directory are postgres:postgres
The file $PGDATA/pg_hba.conf contains a bunch of parameters that allow the configuration of the database system including how authorization is performed. The default entry in pg_hba.conf (as created during #4) is fine for creating datbases and users and letting other (non postgres) users on localhost connect to any Postgres databases created in the postgres system.
Creating a database instance is done with the createdb script. Again, this must be done as the postgres user:
postgres@bash $ createdb database_name
The Postgres database instance created on joslyn (a Sony VAIO pcg-grt25 laptop) is called twig and the postgres db on jacquelyn (an Intel™ based E6400 CPU on a Gigabyte 965GM Series 2 mainboard running Fedora Core Linux is called cube.
Postgres is pretty similar to oracle and it's syntax for creating users is almost the same. The psql script is an interactive sql interpretor. Use this to connect to the db instance created in #6 and then create some users:
postgres@bash $ psql -d cube
This will start the postgres interactive sql interpretor, which is a bit weird. Commands are terminated like oracle commands in that the command terminator is the ";" character. Interative help can be shown with syntax such as \h command. The prompt is dbsid=#. So a command such as
cube=# \h create user
Provides help on the create user syntax. Essentially, a simple form of the create user command in Postgres is:
cube=# create user foo WITH encrypted password 'foo' createdb createuser;
The previous create user command actually creates a role. In the default login case, an OS user say foo which attempts a postgres connection will attempt to login with a rolename which has the same name as the OS user. So it may be useful to create a postgres database user for each OS user that will connect to the Postgres server instance.
The init.d script postgresql can be used to startup the database system defined in the PGDATA variable of the postgres user:
postgres@bash# /etc/init.d/postgresql start | stop | restart
This script estentially runs postmaster which is the controlling script for the postgres system. The init script actuall performs:
postgres@bash $ postmaster -D /u01/postgres/database
Another option is to run pg_ctl as user postgres. This script can reload changes made to the $PGDATA/pg_hba.conf file and it can also be used to start and stop the database system:
To reload configuration changes:
postgres@bash $ pg_ctl reload
To start and stop the postmaster instance:
postgres@bash $ pg_ctl start postgres@bash $ pg_ctl stop
After a database instance and a database user are created, then psql can be used by any user on the localhost (set PGDATA or use the -D option to point to the database system install). The -d (small d) is used to indicate the db sid.
postgres@bash $ psql -d cube
Postgres can be configured to operate in secure mode using SSL. This requires that openSSL be installed on both the Postgres client and the Postgres server. To enable SSL mode, edit the postgresql.conf file (in $PGDATA) and set the variable ssl=on. For SSL to work, a certificate with with private key and signing request must be created and installed. The easiest option for Postgres and ssl is to create a self-signed RSA encoded x509 certificate with private key. These are typically named server, e.g.,:
☆ server.crt
☆ server.req
☆ server.key
Refer to the SSL-TCP documentation in the Postgres Documentation release for more information on creating secure postgres connections. The authentication file pg_hba.conf controls how connections are made. The entries allow control over which hosts can connect to which databases and using which methods (local unix sockets, tcp secure, etc).
A secure connection is established when the tcp connection mode is initiated, which is done whenever the -h host option is specified to psql. For tcp connections, the Postgres Server instance must be listening on the address to which clients will connect. This is done by specifying the listen_address property in the Postgres Server configuration file postgresql.conf. This property includes the localhost by default but other hostnames are specified as a comma separated list. For example, to if the postgres server is running on host jacquelyn, then specify the listen address as:
listen_addresses = 'localhost,192.168.1.100'
In addition to the listen addresss the firewall must also allow tcp connection over the postgres port, which is 5432 by default. The port number can be changed by specifying the port property, also in postgresql.conf, e.g., port=5432
The final step in allowing tcp connections over ssl is to turn the ssl mode on and to specify which database instances are handled by ssl. The former is done by setting ssl=on in postresql.conf and later by adding an authentication entry in pg_hba.conf. The ssl entries are specified with the hostssl entry. For example, to specify that all connections from network 192.168.0.x to any database should be handled by ssl using the trust mode. Configuring the system for local trust authentication allows any local user to connect as any PostgreSQL user, including the database superuser.
hostssl all all 192.168.1.0/24 trust
In summary, ssl connections are made by connecting to the Postgres Server in tcp mode. This is done by specifying the -h hostname option. Consider the difference between the following two postgres connections.
Local unix domain sockets connection:
bash $ psql -d cube Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
cube=#
TCP connection with SSL:
bash $ psql -d cube -h Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
cube=#
Commands like create table, insert, select and so on are quite similar to Oracle. Note though that postgres has an explicit start transaction command (unlike Oracle, which has an implied start transaction with every command). So the following two dbcodelets are very different:
cube=# insert into a values (1);
and:
cube=# start transaction; cube=# insert into a values (1); cube=# commit transaction
Often version upgrades of postgres will be incompatble with prior releases. If this happens, then the database instance must be migrated before the postgres instance can be started. The basic steps in a migration are: dump existing db data to file, upgrade existing db instance to new format, initialize the upgraded instance, retore dumped db data into new instance.
Refer to the postgres release documentation for migration databases between two incompatable releases. Postgres maintains database compatibility between minor releses but not major. Where they define a minor release as the number after the second dot. E.g., 8.0.1 and 8.0.3 are compatible. A major release 8.0.3 and 8.1 does not guarantee database compatibility. Refer to the 8.0.3 release documentation for more information on database migration.
There some good doco for postgres at:
☆ OSMossislatina.com Postgres Part II
☆ PostgreSQL.org FAQ
☆ PostgreSQL.org quckref
☆ PostgreSQL.org Techdocs
Also, the Postgres release documentation is thorough and is located in the usual /usr/share/docs. The documentation is bundled in an html directory of the postgres release. Here is the 8.0.3 release documentation.
Postgres uses the \dt comment to display system catalog information for tables, indexes, databases, and users
Also the source code for psql contained in the file pgsql/src/bin/psql/describe.c, contains SQL commands that generate the output for psql's backslash commands. The file pgsql/src/tutorial/syscat.source illustrates many of the SELECTs needed to get information from the database system tables.
Starting psql with the -E option ensures that postgres will print out any queries it uses when executing user commands. PostgreSQL also provides an SQL compliant INFORMATION SCHEMA interface for querying information about the database. Some notes:
☆ Postgres system tables begin with pg_ such as pg_foo
☆ Running psql -l lists all databases for the current postmaster instance
This section describes some generally useful Postgres commands and SQL. In the examples bash $ indicates the command is a POSIX shell command and db=# indicates the command is run within the postgres environment. In many of the postgres slash "\" commands can take an extended information specifier, which is +. E.g., \dt lists tables, \dt+ lists tables with extended information. Also may of the commands can take an optional pattern. E.g., \dt foo list all tables starting with foo
+---------------------------+----------------------------------------------+ |bash $ psql -l | List all known databases | |---------------------------+----------------------------------------------+ |bash $ psql -d cube -U foo | Connect to a database instance called "cube" | | | as user "foo". If -U is left out, then the | | | connection is made using a db username the | | | same of the current os user. E.g., if logged | | | into the shell as "foo", then the db user | | | for the connection will be "foo" | |---------------------------+----------------------------------------------+ | db=# \d | List all the tables within the current | | | schema (current db user) | |---------------------------+----------------------------------------------+ |db=# \dt x.* | show tables in schema x | |---------------------------+----------------------------------------------+ |db=# \d sometable | Describe the table structure for "sometable" | |---------------------------+----------------------------------------------+ |db=# \dS | List system tables within the current db | |---------------------------+----------------------------------------------+ |db=# \dn | List all schemas within the current database | +---------------------------+----------------------------------------------+
PostgreSQL has similar tools to Oracle's exp and imp for exporting and importing database. The command pg_dump will write a file conataining the contents of the named database instance. The dump command has options for selective export, compression, preserving object ids and so on. The command db_restore will import a previously created database dump.
The following example exports the entire contents of a database named cube and re-imports it to a database named postgres.
bash $ pg_dump -i -h 127.0.0.1 -p 5432 -U foo -F c -b -o -v -f "/tmp/cube.dmp" cube bash $ pg_restore -i -h 127.0.0.1 -p 5432 -U foo -d postgres "/tmp/cube.dmp"
Some support functions are not available by default in all PostgreSQL versions. These functions enable some tasks that make life easier when dealing with log and configuration files.
When compiling from source, the necessary files can be found in the xtra subdirectory of the pgAdmin source tree. For PostgreSQL 8.0, copy the admin directory under the postgresql contrib source directory, make and make install from there. For PostgreSQL 8.1, use the admin81 directory for that. After the module is installed, you need to create the instrumentation functions in your maintenance database using the admin.sql script (called admin81.sql for PostgreSQL 8.1) which are usually located in the pgsql share directory, e.g. /usr/local/pgsql/share.
The amarok media player can store is media information and playlists in Postgres (default is SQLite). The advantage of using Postgres for amarok is that its a proper dbms and is a lot faster than the text-file based SQLite. The process for configuring amarok to use postgres requires that a special database be created and a special user/role be created. These are both typically called Amarok. E.g.,
postgres@bash $ createdb amarok CREATE DATABASE
postgres@bash $ psql -h localhost -d amarok
amarok=# create user amarok WITH encrypted password 'amarok' createdb createuser; CREATE ROLE
amarok=# grant select on admin, album, album_seq, amazon, artist, artist_seq,
composer, composer_seq, devices, devices_seq, directories, embed, genre,
genre_seq, images, labels, labels_seq, lyrics, playlists, podcastchannels,
podcastepisode_seq, podcastepisodes, podcastfolder_seq, podcastfolders,
related_artists, statistics, tags, tags_labels,uniqueid, year, year_seq to public;
amarok=# \d
List of relations Schema | Name | Type | Owner --------+--------------------+----------+-------- public | admin | table | amarok public | album | table | amarok public | album_seq | sequence | amarok public | amazon | table | amarok public | artist | table | amarok public | artist_seq | sequence | amarok public | composer | table | amarok public | composer_seq | sequence | amarok public | devices | table | amarok public | devices_seq | sequence | amarok public | directories | table | amarok public | embed | table | amarok public | genre | table | amarok public | genre_seq | sequence | amarok public | images | table | amarok public | labels | table | amarok public | labels_seq | sequence | amarok public | lyrics | table | amarok public | playlists | table | amarok public | podcastchannels | table | amarok public | podcastepisode_seq | sequence | amarok public | podcastepisodes | table | amarok public | podcastfolder_seq | sequence | amarok public | podcastfolders | table | amarok public | related_artists | table | amarok public | statistics | table | amarok public | tags | table | amarok public | tags_labels | table | amarok public | uniqueid | table | amarok public | year | table | amarok public | year_seq | sequence | amarok
Stuart Moorfoot © 2 Dec 2004 foo@bund.com.au