postgres_83(5) Standards, Environments, and Macros postgres_83(5)NAME
PostgreSQL 8.3 - PostgreSQL RDBMS version 8.3 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, text search, XML support, a sophisticated
query planner/optimizer, and write ahead logging for fault tolerance.
It supports international character sets, multibyte character encod‐
ings, Unicode, and it is locale-aware for sorting, case-sensitivity,
and formatting.
PostgreSQL version 8.3 for Solaris is delivered as part of Entire Dis‐
tribution Software Group (SUNWCall), in the following 10 packages:
┌───────────────────────────────┬────────────────────────────────────┐
│ Package │ Contents │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-83-client │ Client tools │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-83-contrib │ Community contributed tools │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-83-devel │ Development tools and header files │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-83-docs │ Documentation and man pages │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-83-libs │ Client libraries │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-83-pl │ Additional procedural languages │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-83-server │ Database server │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-83-server-data-root │ Database server data directories │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-jdbc │ JDBC driver │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-83-tcl │ Tcl binding library │
├───────────────────────────────┼────────────────────────────────────┤
│SUNWpostgr-upgrade │ Tools for upgrade from version 8.1 │
└───────────────────────────────┴────────────────────────────────────┘
Note that the SUNWpostgr-83-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-83-server package.
Note also that the JDBC driver package is now version independent,
unlike the corresponding package for PostgreSQL 8.2.
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-83S │ PostgreSQL 8.3 (Source) │
├───────────────────┼─────────────────────────────────────────────────────┤
│SUNWpostgr-jdbcS │ JDBC driver (Source) │
├───────────────────┼─────────────────────────────────────────────────────┤
│SUNWpostgr-82-tclS │ Tcl binding library (Source for versions 8.2 & 8.3) │
└───────────────────┴─────────────────────────────────────────────────────┘
None of the 8.3 packages replace any previous version(s) of PostgreSQL
already installed on your system (i.e. PostgreSQL 8.1 or 8.2). You can
run old and new PostgreSQL 8.3 databases simultaneously, allowing a
planned upgrade/migration (refer to the upgrade section below for
details).
FILES AND DIRECTORIES
The PostgreSQL 8.3 software for Solaris is installed into a number of
sub-directories of /usr/postgres/8.3. They are:
┌──────────────────────────┬───────────────────────────────────────────┐
│ Directory │ Contents │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.3/bin │ Client and server commands and utilities │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.3/bin/64 │ 64-bit commands and utilities │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.3/doc │ HTML documentation │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.3/etc │ Sample configuration files │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.3/include │ Development header files │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/jdbc │ JDBC driver │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.3/lib │ Shared libraries │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.3/lib/64 │ 64-bit shared libraries │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.3/man │ Manual pages │
├──────────────────────────┼───────────────────────────────────────────┤
│/usr/postgres/8.3/share │ Shared data: locale, timezone & sql files │
└──────────────────────────┴───────────────────────────────────────────┘
Note that the JDBC driver is an exception, being placed in a version
independent directory.
The following directories are also created by the SUNWpostgr-83-server-
data-root package:
┌──────────────────────────┬────────────────────────────────────────┐
│ Directory │ Contents │
├──────────────────────────┼────────────────────────────────────────┤
│/var/postgres/8.3/data │ Default database data directory │
├──────────────────────────┼────────────────────────────────────────┤
│/var/postgres/8.3/data_64 │ Default 64-bit database data directory │
├──────────────────────────┼────────────────────────────────────────┤
│/var/postgres/8.3/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-83-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_83.xml
/lib/svc/method/postgres/8.3/exec_method
/etc/user_attr
/etc/security/auth_attr
/etc/security/prof_attr
/etc/security/exec_attr
The files associated with the source packages SUNWpostgr-83S, SUNW‐
postgr-jdbcS and SUNWpostgr-82-tclS are installed into the following
directories:
/usr/share/src/postgres/8.3
/usr/share/src/postgres/postgresql-jdbc-8.3-603.src
/usr/share/src/postgres/pgtcl1.5
DEPENDENCIES
The 10 PostgreSQL 8.3 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 │
├──────────────────────┼─────────────────────────────────────────────────┤
│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.3 in preference to 8.1, you need to add the
8.3 directories to the beginning of your PATH and MANPATH environment
variables. For example:
sh & ksh:
example% PATH=/usr/postgres/8.3/bin:$PATH
example% MANPATH=/usr/postgres/8.3/man:$MANPATH
example% export PATH MANPATH
csh:
example% setenv PATH /usr/postgres/8.3/bin:$PATH
example% setenv MANPATH /usr/postgres/8.3/man:$MANPATH
To administer a 64-bit server, you must add the 64-bit bin direc‐
tory /usr/postgres/8.3/bin/64 to the beginning of your PATH.
Note that to execute PostgreSQL 8.3 commands, it is not required to add
the 8.3 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.3 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.3/lib \
-R/usr/postgres/8.3/lib -lpq -o myprog
or if developing a 64-bit client application:
example% cc myprog.c -L/usr/postgres/8.3/lib/64 \
-R/usr/postgres/8.3/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.3 32-bit server data directory:
sh & ksh:
example% PGDATA=/var/postgres/8.3/data
example% export PGDATA
Or if managing the default 8.3 64-bit server:
example% PGDATA=/var/postgres/8.3/data_64
example% export PGDATA
csh:
example% setenv PGDATA /var/postgres/8.3/data
Or if managing the default 8.3 64-bit server:
example% setenv PGDATA /var/postgres/8.3/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
its 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.
If you wish to login directly to the user id, you must enable its pass‐
word 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.3 server is
/var/postgres/8.3/data, which is created for you by the package SUNW‐
postgr-server-data-root. It is 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.3/bin/initdb -D <data directory>
For example:
If managing the default 32-bit database:
example% /usr/postgres/8.3/bin/initdb -D /var/postgres/8.3/data
Or if managing the default 64-bit database:
example% /usr/postgres/8.3/bin/initdb -D /var/post‐
gres/8.3/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.3/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.3 for Solaris includes files necessary to register with
the service management facility described in smf(5). After installation
of the SUNWpostgr-83-server-data-root package, you will have 2 postgres
8.3 service instances registered (disabled by default). The RBAC files
issued with PostgreSQL 8.3 for Solaris provide the "postgres" user id
necessary privileges to manage these 5 service instances. For example,
to list the services instances:
example% svcs postgresql_83
STATE STIME FMRI
disabled Feb_07 svc:/application/database/postgresql_83:default_32bit
disabled Feb_07 svc:/application/database/postgresql_83:default_64bit
To list the configurable properties, and confirm that the instance
named "default_32bit" is configured to manage a PostgreSQL 8.3 database
cluster located in the default directory /var/postgres/8.3/data:
example% svcprop -p postgresql_83 default_32bit
postgresql_83/bin astring /usr/postgres/8.3/bin
postgresql_83/data astring /var/postgres/8.3/data
postgresql_83/log astring server.log
postgresql_83/value_authorization astring solaris.smf.value.postgres
With the SUNWpostgr-82-server-data-root package which is part of Post‐
greSQL version 8.2 for Solaris, there is as similar service "post‐
gresql" used to manage versions 8.1 or 8.2 of PostgreSQL. See post‐
gres_82(5) for further details.
To enable the default_32bit service instance so that the database clus‐
ter located in the default directory /var/postgres/8.3/data is automat‐
ically started & stopped, run the command:
example% svcadm enable postgresql_83:default_32bit
The state of the service instance should change to "online", and the
database server background processes should be started:
example% svcs -a | grep postgresql_83
online 21:28:26 svc:/application/database/postgresql_83:default_32bit
disabled Feb_07 svc:/application/database/postgresql_83:default_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.3/bin/postgres -D /var/postgres/8.3/data
postgres 2267 2265 0 21:28:26 ? 0:00 /usr/postgres/8.3/bin/postgres -D /var/postgres/8.3/data
postgres 2265 1 0 21:28:26 ? 0:00 /usr/postgres/8.3/bin/postgres -D /var/postgres/8.3/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).
If you chose to create your database cluster files in a directory other
than the default /var/postgres/8.3/data, you can change the properties
associated with the default_32bit instance to point to the alternate
data directory:
example% svccfg -s postgresql_83:default_32bit setprop \
postgresql_83/data="/pgdata"
example% svcadm refresh postgresql_83:default_32bit
example% svcprop -p postgresql_83 default_32bit | grep data
postgresql_83/data astring /pgdata
Note that enabling the database instance in the above example requires
/pgdata to be owned by the user 'postgres' and to be not writable by
others.
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_83:default_32bit
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(1) is required for those wishing to
migrate data from any previous release to PostgreSQL 8.3.
For example, if you are currently running a database using PostgreSQL
8.1 software, and wish to upgrade that database to use PostgreSQL 8.3,
you should perform the following steps:
1. Stop all applications accessing the database.
You can for instance edit pg_hba.conf to disallow access to the
database for everybody except yourself.
2. Take a full dump of the database using the (8.1 software)
command pg_dump(1) or pg_dumpall(1).
pg_dumpall > backupfile
3. Shutdown the database using pg_ctl(1) stop or svcadm(1M) dis‐
able.
4. Create a new PostgreSQL 8.3 database (refer to the DATABASE
CREATION section above).
5. Edit the new 8.3 database's postgresql.conf file so that it
listens on the same port as the old 8.1 database.
6. Start the new 8.3 database.
7. Load the data into the new 8.3 database using the (8.3 soft‐
ware) command pg_restore(1).
pg_restore backupfile
8. Restore the pg_hba.conf file you edited in step one, so that
client applications get access to the database.
9. Restart all client applications that were stopped in step
one.
It is also possible to upgrade a PostgreSQL 8.1 database to use Post‐
greSQL 8.2 using the pg_upgrade.sh(1) tool.
For more information about upgrading to PostgreSQL 8.3, see
http://www.postgresql.org/docs/8.3/interactive/install-upgrading.html
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),
pg_upgrade.sh(1), postgres_82(5)REFERENCES
http://www.postgresql.org/
2008-05-01 postgres_83(5)
