Detailed information on the PostgreSQL database software can be found in the Postgres documentation.
Once the PostgreSQL software is installed, you can start the database server.
Download, compile, and install the PostgreSQL database software. Create a user account to own the database processes. Usually, this user is postgres. Add the PostgreSQL bin directory and necessary LD_LIBRARY_PATH settings to your environment.
Create a home directory for the postgres user.
In this example, the home directory is /space/postgres/data.
% mkdir -p /space/postgres/data % useradd -d /space/postgres postgres % chown postgres /space/postgres/data % su - postgres |
Continue as described in the PostgreSQL documentation to set up a database.
> initdb -D /space/postgres/data creating directory /space/postgres/data... ok creating directory /space/postgres/data/base... ok creating directory /space/postgres/data/global... ok creating directory /space/postgres/data/pg_xlog... ok creating directory /space/postgres/data/pg_clog... ok creating template1 database in /space/postgres/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: postmaster -D /space/postgres/data or pg_ctl -D /space/postgres/data -l logfile start |
Make the following changes to the pg_hba.conf file.
This change permits unrestricted and password free access to the database superuser postgres but requires md5 encrypted passwords for all other database users. Replace nnn.nnn.nnn with your subnet address without the trailing 0. You also can add access rules on a per-host basis by adding similar lines with host IP addresses.
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD local all postgres trust local all all md5 # IPv4-style local connections: #host all all nnn.nnn.nnn.0 255.255.255.0 md5
Make the following changes to the postgresql.conf file, to enable TCP/IP access from other hosts.
Ensure that the value of shared_buffers is at least twice the value of max_connections.
tcpip_socket = true max_connections = 40 (increase if necessary)
Start the database.
In this example, -i enables TCP/IP communication, and -S is for silent mode.
> postmaster -S -i |
Verify the installation.
As the postgres user, try the following commands:
% su - postgres > createuser -P test_user Enter password for new user: Enter it again: 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) n CREATE USER > createdb -O test_user -E UNICODE test CREATE DATABASE |
Execute commands as the database super user.
> psql test Welcome to psql 7.3, 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 |
test=# create table test (x int, y text); CREATE TABLE test=# insert into test values (1, 'one'); INSERT 16982 1 test=# insert into test values (2, 'two'); INSERT 16983 1 test=# select * from test; x | y ---+------ 1 | one 2 | two (2 rows) test=# \q > psql -U test_user test Password: Welcome to psql 7.4.1, 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 test=> |
After you have successfully tested your database, you should proceed to the next task, How to Set Up a PostgreSQL Database.
Log in as the database superuser, for example, postgres.
# su - postgres |
Create the owner of the database.
> createuser -P arco_write Enter password for new user: Enter it again: 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) n CREATE USER |
Create the database for accounting and reporting.
> createdb -O arco_write arco CREATE DATABASE |
Create a database user for reading the database.
If you do not use the arco_read user, you need to modify the privileges script that is used in the next step.
> createuser -P arco_read Enter password for new user: Enter it again: Shall the new user be allowed to create databases? (y/n) n Shall the new user be allowed to create more new users? (y/n) n CREATE USER |