PostgreSQL Server

From Oxxus Wiki

Jump to: navigation, search

For more information on PostgreSQL hosting visit our VPS hosting page.

Installing PostgreSQL

After logging on to your VPS server via SSH further steps depend on the Linux distribution your VPS is running on.

In case of CentOS or Fedora, execute the following command as root user:

yum -y install postgresql postgresql-server php-pgsql

In case of Debian or Ubuntu:

apt-get install postgresql

This should install PostgreSQL client and server components as well as PHP module needed for interaction with the database from within PHP.

To start the server type the following for CentOS/Fedora:

service postgresql start

In case of Debian or Ubuntu:

/etc/init.d/postgresql start

Configuring PostgreSQL

PostgreSQL will setup a system user named postgres which is the default administrator account for the server. You can switch to this system user by issuing the following command as root:

su - postgres

Adding users

You can add user either using the command tool named createuser or by issuing SQL commands. Before beginning, obtain superuser privileges by switching to postgres user as indicated in previous section.

Type the following:

createuser <username>

crateuser command tool comes with several options:

-a - allow user to add additional user effectively making it superuser as well
-A - disallow user to add additional users
-d - allow user to create databases
-D - disallow user to create databases
-p - set password for user

To add a superuser account, use the following:

crateuser -a -p myusername

SQL alternative would be to first log on to PostgreSQL server (as system user postgres type):

psql template1

We will use CREATE USER statement, general format is:

 CREATE USER username [ [ WITH ] 
   SYSID uid 
   | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
   | CREATEDB | NOCREATEDB
   | CREATEUSER | NOCREATEUSER
   | IN GROUP groupname [, ...]
   | VALID UNTIL 'abstime' ]

In its simplest form without any options:

CREATE USER myuser;

To add the user with a password:

CREATE USER myuser WITH PASSWORD 'mypassword';

Creating databases

PostgreSQL comes with a command tool that can make the process easier and you can avoid using SQL commands for database creation, this tool is called createdb.

createdb <database name>

You can also create database for someone else:

createdb -O <another user> <database name>

SQL alternative for adding the database:

CREATE DATABASE database_name;

Allowing remote access

To allow remote access to your PostgreSQL server, you need to make some configuration changes to pg_hba.conf and postgresql.conf files. You can find them in either of the two locations depending on the version of the server:

/var/lib/pgsql/data/
/etc/postgresql/X/main/ (where X is the version)

In pg_hba.conf

At the bottom, add the following line:

host all all 1.2.3.4/24 md5

Format of the line can be either of the two:

host       database  user  CIDR-address  auth-method
host       database  user  IP-address  IP-mask  auth-method

First one uses CIDR format for allowed incoming IP and other uses IP and mask. You should enter the IP address range from where you plan to connect or just enter * for all or 0.0.0.0/0.

For example, to allow remote connection to database mydatabase for user myuser from any IP address:

host mydatabase myuser * md5

In postgresql.conf

Find the setting listen_addresses and set it to '*', this will make the server listen on all interfaces instead of the local loopback only.

listen_addresses='*'

After this, server restart is required so execute one of the following depending on your distro:

service postgresql restart

... or ...

/etc/init.d/postgresql restart

Installing phpPgAdmin

To install phpPgAdmin, you can use yum package manager on Centos/Fedora or apt-get on Ubuntu/Debian:

yum -y install phpPgAdmin