postgres_82(5) Standards, Environments, and Macros postgres_82(5)NAME
PostgreSQL 8.2 - PostgreSQL RDBMS version 8.2 for Solaris
DESCRIPTION
PostgreSQL is a powerful, open source relational database system. It is
fully ACID compliant, has full support for foreign keys, joins, views,
triggers, and stored procedures (in multiple languages). It includes
most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN,
CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage
of binary large objects, including pictures, sounds, or video. It has
native programming interfaces for C/C++, Perl, Python, Tcl, ODBC, among
others.
An enterprise class database, PostgreSQL boasts sophisticated features
such as Multi-Version Concurrency Control (MVCC), point in time recov‐
ery, tablespaces, asynchronous replication, nested transactions (save‐
points), online/hot backups, a sophisticated query planner/optimizer,
and write ahead logging for fault tolerance. It supports international
character sets, multibyte character encodings, Unicode, and it is
locale-aware for sorting, case-sensitivity, and formatting.
PostgreSQL version 8.2 for Solaris is delivered as part of Entire Dis‐
tribution Software Group (SUNWCall), in the following 10 packages:
┌───────────────────────────────┬────────────────────────────────────┐
│ Package │ Contents │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-82-client │ Client tools │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-82-contrib │ Community contributed tools │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-82-devel │ Development tools and header files │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-82-docs │ Documentation and man pages │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-82-libs │ Client libraries │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-82-pl │ Additional procedural languages │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-82-server │ Database server │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-82-server-data-root │ Database server data directories │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-82-jdbc │ JDBC 3 driver │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-82-tcl │ Tcl binding library │
└───────────────────────────────┴────────────────────────────────────┘
Note that the SUNWpostgr-82-pl additional procedural languages avail‐
able with a 32-bit server are: plperl, pltcl & plpython. The additional
procedural language available with a 64-bit server is plpython. The
pltcl & plperl languages are not yet available for 64-bit server. The
plpgsql procedural language is available for both 32-bit & 64-bit
server with the SUNWpostgr-82-server package.
There are 3 additional source packages that are not installed by
default, but are available on the Solaris distribution media. They are:
┌────────────────────┬───────────────────────────────────────────┐
│ Package │ Contents │
├────────────────────┼───────────────────────────────────────────┤
│SUNWpostgr-82S │ PostgreSQL 8.2 (Source) │
├────────────────────┼───────────────────────────────────────────┤
│SUNWpostgr-82-jdbcS │ JDBC 3 driver for PostgreSQL 8.2 (Source) │
├────────────────────┼───────────────────────────────────────────┤
│SUNWpostgr-82-tclS │ Tcl binding library (Source) │
└────────────────────┴───────────────────────────────────────────┘
None of the 8.2 packages replace any previous version(s) of PostgreSQL
already installed on your system (i.e. PostgreSQL 8.1). You can run old
8.1 and new PostgreSQL 8.2 databases simultaneously, allowing a planned
upgrade/migration (refer to the upgrade section below for details).
FILES AND DIRECTORIES
The PostgreSQL 8.2 software for Solaris is installed into a number of
sub-directories of /usr/postgres/8.2. They are:
┌──────────────────────────┬───────────────────────────────────────────┐
│ Directory │ Contents │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.2/bin │ Client and server commands and utilities │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.2/bin/64 │ 64-bit commands and utilities │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.2/doc │ HTML documentation │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.2/etc │ Sample configuration files │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.2/include │ Development header files │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.2/jdbc │ JDBC driver │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.2/lib │ Shared libraries │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.2/lib/64 │ 64-bit shared libraries │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.2/man │ Manual pages │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.2/share │ Shared data: locale, timezone & sql files │
└──────────────────────────┴───────────────────────────────────────────┘
The following directories are also created by the SUNWpostgr-82-server-
data-root package:
┌──────────────────────────┬────────────────────────────────────────┐
│ Directory │ Contents │
├──────────────────────────┼────────────────────────────────────────┤
│/var/postgres/8.2/data │ Default database data directory │
├──────────────────────────┼────────────────────────────────────────┤
│/var/postgres/8.2/data_64 │ Default 64-bit database data directory │
├──────────────────────────┼────────────────────────────────────────┤
│/var/postgres/8.2/backup │ Default database backup directory │
└──────────────────────────┴────────────────────────────────────────┘
Note that a database created with 32-bit binaries cannot subsequently
be managed by 64-bit binaries, and visa-versa. Any attempt to start a
database with incompatible binaries will result in the following error:
FATAL: incorrect checksum in control file
The SUNWpostgr-82-server-data-root package also installs the following
SMF and RBAC files to facilitate automatic service management (refer to
smf(5), rbac(5) and the SMF section below):
/var/svc/manifest/application/database/postgresql.xml
/lib/svc/method/postgresql
/etc/user_attr
/etc/security/auth_attr
/etc/security/prof_attr
/etc/security/exec_attr
The files associated with the source packages SUNWpostgr-82S, SUNW‐
postgr-82-jdbcS and SUNWpostgr-82-tclS are installed into the following
directories:
/usr/share/src/postgres/8.2
/usr/share/src/postgres/postgresql-jdbc-8.2-504.src
/usr/share/src/postgres/pgtcl1.5
DEPENDENCIES
The 10 PostgreSQL 8.2 software packages listed above are dependent upon
a number of Solaris packages (in addition to the Core Solaris pack‐
ages). These required packages are also installed as part of the
Entire Distribution Software Group (SUNWCall), so should already be
present on your system. They are:
┌──────────────────────┬─────────────────────────────────────────────────┐
│ Package │ Contents │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWPython │ The Python interpreter, libraries and utilities │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWTcl │ Tcl - Tool Command Language │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWTk │ Tk - TCL GUI Toolkit │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWj5rt │ Java Platform vm and core class libraries │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWkrbu │ Kerberos version 5 support (Usr) │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWlibms │ Math & Microtasking Libraries (Usr) │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWlibmsr │ Math & Microtasking Libraries (Root) │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWlxml │ The XML library │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWlxsl │ The XSLT library │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWopenssl-libraries │ OpenSSL Libraries (Usr) │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWgss │ GSSAPI V2 │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWzlib │ The Zip compression library │
├──────────────────────┼─────────────────────────────────────────────────┤
│SUNWperl584core │ Perl 5.8.4 (core) │
└──────────────────────┴─────────────────────────────────────────────────┘
ENVIRONMENT
The files for the PostgreSQL 8.1 packages for Solaris are installed
into the default paths /usr/bin, /usr/lib, /usr/man, etc. Therefore by
default you will use PostgreSQL version 8.1.
To use PostgreSQL version 8.2 in preference to 8.1, you need to add the
8.2 directories to the beginning of your PATH and MANPATH environment
variables. For example:
sh & ksh:
example% PATH=/usr/postgres/8.2/bin:$PATH
example% MANPATH=/usr/postgres/8.2/man:$MANPATH
example% export PATH MANPATH
csh:
example% setenv PATH /usr/postgres/8.2/bin:$PATH
example% setenv MANPATH /usr/postgres/8.2/man:$MANPATH
To administer a 64-bit server, you must add the 64-bit bin direc‐
tory /usr/postgres/8.2/bin/64 to the begining of your PATH.
Note that to execute PostgreSQL 8.2 commands, it is not required to add
the 8.2 lib directory to your LD_LIBRARY_PATH environment variable.
If you are developing your own client applications using the libpq
client library, then you will need to include the 8.2 lib directory (or
lib/64 directory if developing 64-bit applications) in your compiler
options. For example:
example% cc myprog.c -L/usr/postgres/8.2/lib \
-R/usr/postgres/8.2/lib -lpq -o myprog
or if developing a 64-bit client application:
example% cc myprog.c -L/usr/postgres/8.2/lib/64 \
-R/usr/postgres/8.2/lib/64 -lpq -o myprog
Most PostgreSQL server commands accept the -D argument, which specifies
the location of the database data directory. You can override the
default value, and can omit the -D argument to all server commands by
setting the PGDATA environment variable. For example, to make all
server commands use the default 8.2 32-bit data directory:
sh & ksh:
example% PGDATA=/var/postgres/8.2/data
example% export PGDATA
Or if managing the default 8.2 64-bit server:
example% PGDATA=/var/postgres/8.2/data_64
example% export PGDATA
csh:
example% setenv PGDATA /var/postgres/8.2/data
Or if managing the default 8.2 64-bit server:
example% setenv PGDATA /var/postgres/8.2/data_64
DATABASE SERVER USERNAME
The PostgreSQL server will not run as the operating system root user
id. It is therefore recommended that the database server is run under
it's own dedicated user id called "postgres".
If you are running Solaris 10 8/07 (or later), your system should
already have the "postgres" operating system user id and group in the
/etc/passwd and /etc/group files (using the reserved uid & gid value of
90).
If your system does not already include the "postgres" operating system
user id and group, you should create them as follows (note that you
must be logged in as the root user id, and the warning messages can be
ignored):
root# groupadd -g 90 postgres
UX: groupadd: WARNING: gid 90 is reserved.
root# useradd -c "PostgreSQL Reserved UID" -d / -g 90 \
-u 90 -s /usr/bin/pfksh postgres
UX: useradd: WARNING: uid 90 is reserved.
root# passwd -N postgres
passwd: password information changed for postgres
This will create the "postgres" user id exactly as it exists in Solaris
10 8/07 (or later), which is actually a role with logins disabled.
This means you cannot login directly to the user id. To assume the
identity of the "postgres" user id you must "su" from a privileged user
such.
If you wish to login directly to the user id, you must enable it's
password by running the command (note that you must be logged in as the
root user id):
root# passwd -d postgres
passwd: password information changed for postgres
You must also change it from a role to a user by editing the file
/etc/user_attr and changing the "postgres" entry:
from:
postgres::::type=role;profiles=Postgres Administration,All
to:
postgres::::type=user;profiles=Postgres Administration,All
The next time you login directly to the "postgres" user id, you will be
prompted to enter a new password.
DATABASE CREATION
You must first choose (and create, if necessary) your database data
directory which will hold your database files. The database data direc‐
tory must be owned by the user id that calls 'initdb', and have permis‐
sions of 0700.
The default data directory for a 32bit PostgreSQL 8.2 server is
/var/postgres/8.2/data, which is created for you by the package SUNW‐
postgr-server-data-root. It is be owned by the "postgres" user id, and
has permissions of 0700.
Login (or su) to the database server user id "postgres" (see DATABASE
SERVER USERNAME section above). Initialize the database files by run‐
ning following command:
example% /usr/postgres/8.2/bin/initdb -D <data directory>
For example:
If managing the default 32-bit database:
example% /usr/postgres/8.2/bin/initdb -D /var/postgres/8.2/data
Or if managing the default 64-bit database:
example% /usr/postgres/8.2/bin/initdb -D /var/post‐
gres/8.2/data_64
Note that you can omit the -D argument if you set the PGDATA environ‐
ment variable (see ENVIRONMENT section above).
STARTING THE DATABASE INSTANCE
Login (or su) to the database server user id "postgres" (see DATABASE
SERVER USERNAME section above).
Note that if you already have another database instance running and
listening on the default port 5432, you must edit the $PGDATA/post‐
gresql.conf file and change the listen port before you can start
another instance. Failing to do this will result in a failure to start
an additional instance with the error:
LOG: could not bind IPv4 socket: Address already in use
HINT: Is another postmaster already running on port 5432? If not, wait
a few seconds and retry.
WARNING: could not create listen socket for "localhost"
FATAL: could not create any TCP/IP sockets
Start the database server background processes by running:
example% pg_ctl -D <data directory> start
For example:
example% pg_ctl -D /var/postgres/8.2/data start
Note that you can omit the -D argument if you set the PGDATA environ‐
ment variable (see ENVIRONMENT section above).
AUTOMATIC SERVICE MANAGEMENT (SMF)
PostgreSQL 8.2 for Solaris includes files necessary to register with
the service management facility described in smf(5). After installation
of the SUNWpostgr-82-server-data-root package, you will have 3 postgres
service instances registered (disabled by default). The RBAC files
issued with PostgreSQL 8.2 for Solaris provide the "postgres" user id
necessary privileges to manage these 3 service instances. For example,
to list the services instances:
example% svcs postgresql
STATE STIME FMRI
disabled Dec_07 svc:/application/database/postgresql:version_81
disabled Dec_07 svc:/application/database/postgresql:version_82
disabled Dec_07 svc:/application/database/postgresql:version_82_64bit
To list the configurable properties, and confirm that the instance
named "version_82" is configured to manage a PostgreSQL 8.2 database
cluster located in the default directory /var/postgres/8.2/data:
example% svcprop -p postgresql version_82
postgresql/bin astring /usr/postgres/8.2/bin
postgresql/data astring /var/postgres/8.2/data
postgresql/log astring server.log
postgresql/value_authorization astring solaris.smf.value.postgres
To list the configurable properties, and confirm that the instance
named "version_81" is configured to manage a PostgreSQL 8.1 database
cluster located in the default directory /var/lib/pgsql/data:
example% svcprop -p postgresql version_81
postgresql/bin astring /usr/bin
postgresql/data astring /var/lib/pgsql/data
postgresql/log astring server.log
postgresql/value_authorization astring solaris.smf.value.postgres
To enable the version_82 service instance so that the database cluster
located in the default directory /var/postgres/8.2/data is automati‐
cally started & stopped, run the command:
example% svcadm enable postgresql:version_82
The state of the service instance should change to "online", and the
database server background processes should be started:
example% svcs -a | grep postgres
disabled Dec_07 svc:/application/database/postgresql:version_81
online 21:28:26 svc:/application/database/postgresql:version_82
disabled Dec_07 svc:/application/database/postgresql:version_82_64bit
example% ps -fu postgres
UID PID PPID C STIME TTY TIME CMD
postgres 2268 2265 0 21:28:26 ? 0:00 /usr/postgres/8.2/bin/postgres -D /var/postgres/8.2/data
postgres 2267 2265 0 21:28:26 ? 0:00 /usr/postgres/8.2/bin/postgres -D /var/postgres/8.2/data
postgres 2265 1 0 21:28:26 ? 0:00 /usr/postgres/8.2/bin/postgres -D /var/postgres/8.2/data
Note that if you don't initialize the database files yourself by run‐
ning initdb before enabling the service (i.e the data directory is
empty when the start method executes), then the database files will be
initialized for you automatically (by running initdb with default
parameters). Note that if you don't initialize the database files
yourself by running initdb before enabling the service (i.e the data
directory is empty when the start method executes), then the database
files will be initialized automatically by running initdb with default
parameters.
If you chose to create your database cluster files in a directory other
than the default /var/postgres/8.2/data, you can change the properties
associated with the version_82 instance to point to the alternate data
directory:
example% svccfg -s postgresql:version_82 setprop \
postgresql/data="/pgdata"
example% svcadm refresh postgresql:version_82
example% svcprop -p postgresql version_82 | grep data
postgresql/data astring /pgdata
To shutdown the database instance and prevent automatic restart (for
example during scheduled downtime for maintenance), disable the SMF
service by running:
example% svcadm disable postgresql:version_82
To assign the necessary privileges to manage the PostgreSQL SMF service
instances to other usernames, run the following command as root:
# usermod -P "Postgres Administration"
UPGRADE FROM PREVIOUS VERSIONS
A dump/restore using pg_dump is required for those wishing to migrate
data from any previous release to PostgreSQL 8.2.
For example, if you are currently running a database using PostgreSQL
8.1 software, and wish to upgrade that database to use PostgreSQL 8.2,
you should perform the following steps:
1. Stop all applications accessing the database
2. Take a full dump of the database using the (8.1 software)
command pd_dump
3. Shutdown the database
4. Create a new PostgreSQL 8.2 database (refer to the DATABASE
CREATION section above).
5. Edit the new 8.2 database's postgresql.conf file so that it
listens on the same port as the old 8.1 database
6. Start the new 8.2 database
7. Load the data into the new 8.2 database using the (8.2 soft‐
ware) command pg_restore
8. Restart all applications
SEE ALSOsmf(5), rbac(5), initdb(1), pg_ctl(1), svcs(1), svcprop(1), svccfg(1),
svcadm(1), postgres(1), postmaster(1), pg_dump(1), pg_restore(1)REFERENCES
http://www.postgresql.org
postgres_82(5)
