Andrew Hudson - Fedora™ Unleashed, 2008 edition

postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

Otherwise, you need to create a user called postgresduring the installation. This user shouldn't have login privileges because only root should be able to use suto become this user and no one will ever log in directly as the user. (Refer to Chapter 10, "Managing Users," for more information on how to add users to a Fedora system.) After you have added the user, you can install each of the PostgreSQL RPMs you downloaded using the standard rpm -icommand for a default installation.

After the RPMs are installed, you need to initialize the data directory. To do so, you must first create the data directory and you must be the root user. The following example assumes that the data directory is /usr/local/pgsql/data.

Create the /usr/local/pgsql/datadirectory (using mkdir) and change the ownerships of the directory (using chownand chgrp) so it is owned by the user postgres. Then use suand, as the user postgres, issue the following commands:

# mkdir /usr/local/pgsql

# chown postgres /usr/local/pgsql

# chgrp postgres /usr/local/pgsql

# su - postgres

-bash-2.05b$ initdb -D /usr/local/pgsql/data

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.

This locale setting will prevent the use of indexes for pattern matching

operations. If that is a concern, rerun initdb with the collation order

set to "C". For more information see the Administrator's Guide.

creating directory /usr/local/pgsql/data... ok

creating directory /usr/local/pgsql/data/base... ok

creating directory /usr/local/pgsql/data/global... ok

creating directory /usr/local/pgsql/data/pg_xlog... ok

creating directory /usr/local/pgsql/data/pg_clog... ok

creating template1 database in /usr/local/pgsql/data/base/1... ok

creating configuration files... ok initializing pg_shadow... ok

enabling unlimited row size for system tables... ok

initializing pg_depend... ok

creating system views... ok

loading pg_description... ok

creating conversions... ok

setting privileges on built-in objects... ok

vacuuming database template1... ok

copying template1 to template0... ok

Success. You can now start the database server using:

/usr/bin/postmaster -D /usr/local/pgsql/data

or

/usr/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

This initializes the database and sets the permissions on the data directory to their correct values.

The initdbprogram sets the permissions on the data directory to 700. You should not change these permissions to anything else to avoid creating a security hole.

You can start the postmasterprogram with the following command (make sure that you are still the user postgres):

$ postmaster -D /usr/local/pgsql/data &

If you have decided to use a directory other than /usr/local/pgsql/dataas the data directory, you should replace the directory in the postmastercommand line with what ever directory you are using.

By default, Fedora makes the PostgreSQL data directory /var/lib/pgsql/data. This isn't a very good place to store the data, however, because most people do not have the necessary space in the /varpartition for any kind of serious data storage. Note that if you do change the data directory to something else (such as /usr/local/pgsql/data, as in the examples in this section), you need to edit the PostgreSQL startup file (named postgres) located in /etc/rc.d/init.dto reflect the change.

Creating a database in PostgreSQL is straightforward, but it must be performed by a user who has permissions to create databases in PostgreSQL — for example, initially the user named postgres. You can then simply issue the following command from the shell prompt (not the PSQL client prompt, but a normal shell prompt):

# su – postgres

-bash-2.05b$ createdb database

where database is the name of the database you want to create.

The createdbprogram is actually a wrapper that makes it easier to create databases without having to log in and use psql. However, you can also create databases from within psql with the CREATE DATABASEstatement. Here's an example:

CREATE DATABASE database;

You need to create at least one database before you can start the pgsqlclient program. You should create this database while you're logged in as the user postgres. To log in as this user, you need to use suto become root and then use suto become the user postgres. To connect to the new database, you start the psqlclient program with the name of the new database as a command-line argument, like so:

$ psql sampledata

If you don't specify the name of a database when you invoke psql, the command attempts to connect to a database that has the same name as the user as which you invoke psql(that is, the default database).

To create a database user, you use suto become the user postgresfrom the Linux root account. You can then use the PostgreSQL createusercommand to quickly create a user who is allowed to access databases or create new database users, like this:

$ createuser phudson

Shall the new user be allowed to create databases? (y/n) y

Shall the new user be allowed to create more new users? (y/n) y

CREATE USER

In this example, the new user named phudsonis created and allowed to create new data bases and database users (you should carefully consider who is allowed to create new databases or additional users).

You can also use the PostgreSQL command-line client to create a new user by typing psqlalong with name of the database and then use the CREATE USERcommand to create a new user. Here is an example:

CREATE USER foobar ;

PostgreSQL allows you to omit the with passwordportion of the statement. However, doing so causes the user to be created with no password. This is a security hole, so you should always use the with passwordoption when creating users.

When you are finished working in the psqlcommand-line client, you can type \q to get out of it and return to the shell prompt.