PostgreSQL installation manual
Author: Nediam <javier@nediam.com.mx>
Publication date: 2005-11-07
Last updated: 2005-12-29
Note: The operating system we used for testing was Debian GNU/Linux.
-
Download the sources from the official PostgreSQL site http://www.postgresql.org/download/. At the time of this writing, the most recent version of PostgreSQL is 8.0.4.
- Supposing the *.tar.bz2 file was downloaded to /usr/local/, extract it and decompress it:
SERVER:~# cd /usr/local
SERVER:/usr/local# bzcat postgresql-8.0.4.tar.bz2 | tar -xvf -
- Create the Makefile, do the compilation and copy the executables to the right locations:
SERVER:/usr/local# cd postgresql-8.0.4
SERVER:/usr/local/postgresql-8.0.4# ./configure
SERVER:/usr/local/postgresql-8.0.4# make
SERVER:/usr/local/postgresql-8.0.4# make install
Tip: it's advisable to first do a ./configure --help in order to check the options we can specify as parameters. - If no errors occurred, PostgreSQL is now installed. To run the database server it is indispensable that it does not run as root, so let's create a system user named postgres
(it could be any name):
SERVER:~# useradd postgres
In the file /etc/passwd configure the line of the user we just created:
postgres:x:1001:1001::/var/pgsql/data:/bin/bash
Assign this user a password using the passwd command, then add in the file /etc/group the group that was assigned to him /etc/passwd (in this case 1001):
postgres:x:1001:
- The next step is to create a PostgreSQL database cluster which will contain the databases we will be creating. At the moment of initialize a PostgreSQL database cluster the directory
where the cluster will be created must be specified. The owner of that directory must be a non-root user. Since we indicated in /etc/passwd that the HOME of postgres user
is /var/pgsql/data, continuing with this logic we will indicate that that would be the directory where the database cluster will be located. So let's create this directory and change
its owner:
SERVER:~# mkdir -p /var/pgsql/data
SERVER:~# chown postgres /var/pgsql/data
Now as user postgres initialize the database cluster with the command initdb:
SERVER:~# su - postgres
postgres@SERVER:~$ /usr/local/pgsql/bin/initdb -D /var/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 C. fixing permissions on existing directory /var/pgsql/data ... ok creating directory /var/pgsql/data/global ... ok creating directory /var/pgsql/data/pg_xlog ... ok creating directory /var/pgsql/data/pg_xlog/archive_status ... ok creating directory /var/pgsql/data/pg_clog ... ok creating directory /var/pgsql/data/pg_subtrans ... ok creating directory /var/pgsql/data/base ... ok creating directory /var/pgsql/data/base/1 ... ok creating directory /var/pgsql/data/pg_tblspc ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 1000 creating configuration files ... ok creating template1 database in /var/pgsql/data/base/1 ... 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 creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: /usr/local/pgsql/bin/postmaster -D /var/pgsql/data or /usr/local/pgsql/bin/pg_ctl -D /var/pgsql/data -l logfile start - What is next is to initialize the database server. Before doing this it's recommended to add the following lines to the profile of the postgres user, so we don't have to enter the
full path where the DB cluster is located every time we initialize the database server, and also to add to his PATH the directory where the PostgreSQL commands are located:
PGDATA=/var/pgsql/data PATH=/usr/local/pgsql/bin:$PATH export PATH PGDATA
Tip: Keep in mind that the HOME we assigned to postgres user is /var/pgsql/data, so his .bash_profile file should be located in this directory.
Now, as user postgres, initialize the database server with the command pg_ctl:
postgres@SERVER:~$ pg_ctl startpostmaster starting postgres@SERVER:~$ LOG: database system was shut down at 2005-11-02 13:21:57 CST LOG: checkpoint record is at 0/A38A34 LOG: redo record is at 0/A38A34; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 544; next OID: 17230 LOG: database system is ready
- All set! We now have PostgreSQL working. To connect to a PostgreSQL database server use the command psql. Many parameters can be pass to this command in the form
psql [OPTIONS]... [DBNAME [USERNAME]]. Let's begin by connecting to our new database server we have just started. Once inside the PostgreSQL terminal we will assign a password to the
PostgreSQL privileged user:
postgres@SERVER:~$ psql template1
Welcome to psql 8.0.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit template1=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'secret'; ALTER USER template1=# \qTip: template1 is the name of one of the two databases that are in the database cluster by default. Among other things, the database template1 is used when one creates a new database. For more information check out this section of the PostgreSQL documentation. -
From the previous point it is plain that to get out of the PostgreSQL terminal the command \q is used. Now, in the system terminal, try to connect again (using the command
psql template1). What happened? Why didn't we got asked to enter password if we have already set it? The answer is that there is a configuration file called pg_hba.conf
where it is specified who can access, how, and what can be accessed from our database server, and by default it is specified that all local connections be accepted without password.
Open this file and look it over thoroughly. If you would like to know more about how authentication works in PostgreSQL, as well as the meaning of the fields that form the records in this file,
take a look at
this
section of the PostgreSQL.
In the meantime, let us suppose we only want to accept local connections and connections from the host with IP 192.168.1.68 (this could be the IP address of the server that hosts our PHP aplication that interacts with our database server) and that the authentication method uses the MD5 encryption algorithm. Then the pg_hba.conf file would contain the following lines:
# TYPE DATABASE USER CIDR-ADDRESS METHOD local all all md5 host all all 192.168.1.68 255.255.255.255 md5 host all all 0.0.0.0 0.0.0.0 reject
After making these changes we need to restart the database server. While we are at it we will also specify the name of the file where we want to store the logs, since formerly all messages were displayed on screen and not saved. We will pass the name of the log file as a parameter to the pg_ctl command (in this case the name of the file will be log_2005-11-02):
postgres@SERVER:~$ pg_ctl stop
LOG: received smart shutdown request
LOG: shutting down
waiting for postmaster to shut down....LOG: database system is shut down
done
postmaster stopped
postgres@SERVER:~$ pg_ctl start -l log_2005-11-02
postmaster starting - Another important file that needs to be checked is the configuration file postgresql.conf. In this file we can configure the port where the server will listen, the maximum
number of connections that it will allow, settings related to system resources, logs, among other things. The options commented are the default values. To change any of these, discomment the
line and specify the new value. If any change is made, the database server has to be restarted.
Tip: For 7.x versions of PostgreSQL, in order to accept TCP/IP connections, the following option has to be set: tcpip_socket = true. - To connect from another computer to our database server, the computer must have the PostgreSQL client installed. Let us suppose the server's IP address is 192.168.1.67. This is how we
would connect from another computer to our PostgreSQL server to the database called db_inventory with the PostgreSQL user peter:
user@HOSTX:~$ psql -h 192.168.1.67 db_inventary peter
Tip: instead of specifying the server's IP address, you can also use domain name of the server. - 2005-11-30: Thanks to Leonel his comment (from the spanish version of this site) regarding how to configure the
init script for PostgreSQL:
"debian has a file called /etc/init.d/skeleton which is a template to make your own init script; in this case making your /etc/init.d/postgresql is simply a matter of copying and editing it as needed and adding it to the startup sequence with update-rc.d
Or id you don't want to use that skeleton script, PostgreSQL has its own. It just has to be copied. It is found where the source was extracted inside contrib/start-scripts; copy the one named linux to /etc/init.d/postgresql and edit it and add it with update-rc.d postgresql defaults"
Final notes:
For a more comprehensive knowledge of PostgreSQL installation, it is recommended to carefully look at its documentation.
What will come next would be to do conection tests from a PHP, Perl, Python or another external application. On the other hand, if you like working with a graphic interphase rather than from
shell, check the
pgadmin
application. It is like a mix between DB Enterprise Manager and Query Analyzer from MS SQL Server. Other people also like to use the application
phpPgAdmin
, which is very similar to phpMyAdmin. To make ODBC connections, the driver I have used is
psqlODBC.
References:
- PostgreSQL Documentation - http://www.postgresql.org/docs/8.0/static/index.html
The latest version of this document is available at: http://nediam.com.mx/en/docs/postgresql_manual/index.php