PostgreSQL Developer's Handbook2001

Usually, RPM packages of PostgreSQL contain scripts for starting and shutting down PostgreSQL properly.

Using a Startup Script

First you check whether the postmaster is running. On UNIX systems, this can easily be done with the following command:

[root@duron /root]# /etc/rc.d/init.d/postgresql status postmaster (pid 4114 4089 4086 4085 4083 4082 4081 4080 4079 4075) is running...

This command works for RedHat 7.1 systems with PostgreSQL RPM packages installed. In the example, you can see that the postmaster is up and running. You can also check whether PostgreSQL is running by using a command like this one:

[hs@duron hs]$ ps ax grep postmaster 4075 ? SN 0:00 /usr/local/postgres/bin/postmaster -i -D /data/postgres 14111 pts/0 S 0:00 grep postmaster

Simply pipe the process table (try ps ax on Linux) to the grep command by using your favorite UNIX shell.

Note

grep commands can differ slightly on various UNIX versions, such as IBM's AIX or Sun Solaris. Many UNIX systems require a hyphen ( - ) before the option (for example, ps -ax ). Check out the man pages for further information about grep on your system.

To start the server, you can use this command:

[root@duron /root]# /etc/rc.d/init.d/postgresql start

Shutting down the server can be done by using stop instead of start .

Using pg_ctl Directly

If you don't have init scripts installed on your machine or if you want to have a more flexible way for starting your server, you can use pg_ctl directly.

Here is an overview of the command's syntax:

[root@athlon /root]# pg_ctl --help pg_ctl is a utility to start, stop, restart, and report the status of a PostgreSQL server. Usage: pg_ctl start [-w] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"] pg_ctl stop [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE] pg_ctl restart [-w] [-D DATADIR] [-s] [-m SHUTDOWN-MODE] [-o "OPTIONS"] pg_ctl status [-D DATADIR] Common options: -D DATADIR Location of the database storage area -s Only print errors, no informational messages -w Wait until operation completes -W Do not wait until operation completes (The default is to wait for shutdown, but not for start or restart.) If the -D option is omitted, the environment variable PGDATA is used. Options for start or restart: -l FILENAME Write (or append) server log to FILENAME. The use of this option is highly recommended. -o OPTIONS Command line options to pass to the postmaster (PostgreSQL server executable) -p PATH-TO-POSTMASTER Normally not necessary Options for stop or restart: -m SHUTDOWN-MODE May be 'smart', 'fast', or 'immediate' Shutdown modes are: smart Quit after all clients have disconnected fast Quit directly, with proper shutdown immediate Quit without complete shutdown; will lead to recovery run on restart Report bugs to <pgsql-bugs@postgresql.org>.

Let's try to start PostgreSQL. Use -D to define the directory where your PostgreSQL databases can be found:

[root@athlon /root]# pg_ctl -D /data/postgresql/ start postmaster successfully started "root" execution of the PostgreSQL server is not permitted. The server must be started under an unprivileged userid to prevent a possible system security compromise. See the INSTALL file for more information on how to properly start the server.

You can see that an error has occurred, because the PostgreSQL daemon must not be launched as root .

For security reasons, you should create a user called postgres that is used solely by the PostgreSQL daemon. Switch to user postgres and try to start the server again ”this time you use some additional parameters:

bash-2.04$ pg_ctl -D /data/postgresql/ -o "-i" -l /tmp/postgresql.log start postmaster successfully started

As you can see, the server has successfully been started. This time you have also passed some additional options to pg_ctl . -o tells the server to pass -i to the backend process. -i makes PostgreSQL backend processes listen to remote hosts so that the database cannot be used only locally. Don't forget this flag or there will be no way to make PostgreSQL listen to remote requests , even if you add entries to pg_hba.conf . (You take a close look at this file in Chapter 6, "Database Administration.") -l defines a file PostgreSQL will send the logging information to. Using -l is recommended because you might need it for debugging purposes.

Creating Databases

You create databases with the createdb command. Here is an overview of its syntax:

[hs@athlon hs]$ createdb --help createdb creates a PostgreSQL database. Usage: createdb [options] dbname [description] Options: -D, --location=PATH Alternative place to store the database -T, --template=TEMPLATE Template database to copy -E, --encoding=ENCODING Multibyte encoding for the database -h, --host=HOSTNAME Database server host -p, --port=PORT Database server port -U, --username=USERNAME Username to connect as -W, --password Prompt for password -e, --echo Show the query being sent to the backend -q, --quiet Don't write any messages By default, a database with the same name as the current user is created. Report bugs to <pgsql-bugs@postgresql.org>.

If the postmaster is up and running, you can log in as user postgres and type createdb name , with name being the name for your database in your UNIX shell:

bash-2.04$ createdb name CREATE DATABASE

If your result looks like the preceding line, you have successfully created a new PostgreSQL database. If you received a result like this, it didn't work:

[root@duron /root]# createdb name psql: FATAL 1: SetUserId: user 'root' is not in 'pg_shadow' createdb: database creation failed

In this example, user root doesn't have the privilege to create databases because there is no user called root in the system tables. You will learn how to set permissions for your PostgreSQL box later in this book.

The name of your PostgreSQL database can be up to 32 characters long. The first character in the name has to be an alphabetic one. You can choose any name for your database, but it's wise to use one that describes what the database is being used for.

Sometimes it is necessary not to use the standard character set. By default, PostgreSQL uses SQL_ASCII; in some cases this won't be the right character set, and you have to tell PostgreSQL to use a different one.

This example shows how to create a database that uses UNICODE as the default character set:

[hs@athlon hs]$ createdb --encoding=UNICODE myunicode CREATE DATABASE

To see whether the database has been created successfully, you use this command ( psql -l lists all databases that are currently available on the system):

[hs@athlon hs]$ psql -l List of databases Database Owner Encoding ------------+-------+----------- db erich SQL_ASCII myunicode hs UNICODE template0 hs SQL_ASCII template1 hs SQL_ASCII (4 rows)

As you can see, the database myunicode uses UNICODE instead of SQL_ASCII .

Login

If you have successfully created a new database, you can connect yourself to it. Become user postgres and type psql name , with name being the name of the database you want to connect to, into your UNIX shell:

bash-2.04$ psql name Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit name=#

If your screen now looks something like this example, you have successfully connected to your PostgreSQL database. If you get an error, you have possibly tried to connect to the database as the wrong user or you haven't created your database correctly.

Let's try a simple query to see whether PostgreSQL works:

name=# SELECT 1+1; ?column? ---------- 2 (1 row)

1+1 makes two ”it seems the database works correctly so far.