Install PostgreSQL on Centos/Ubuntu

Install PostgreSQL on Centos

Configure your YUM repository

edit: /etc/yum.repos.d/CentOS-Base.repo, [base] and [updates] sections

exclude=postgresql*

Install PGDG RPM file

yum localinstall http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

Install PostgreSQL

yum list postgres*

yum install postgresql94-server

Data Directory

The PostgreSQL data directory contains all of the data files for the database. The variable PGDATA is used to reference this directory.

For PostgreSQL version 9.0 and above, the default data directory is:

/var/lib/pgsql/<name>/data

Initialise PostgreSQL

The first command (only needed once) is to initialize the database in PGDATA.

service <name> initdb

E.g. for version 9.4:

service postgresql-9.4 initdb

If the previous command did not work, try directly calling the setup binary, located in a similar naming scheme:

/usr/pgsql-y.x/bin/postgresqlyx-setup initdb

E.g. for version 9.4:

/usr/pgsql-9.4/bin/postgresql94-setup initdb

RHEL 7.1+ and CentOS 7.1+ are a bit different. Use:

postgresql-setup initdb

Start on boot

If you want PostgreSQL to start automatically when the OS starts:

chkconfig <name> on

E.g. for version 9.4:

chkconfig postgresql-9.4 on

Install PostgreSQL on Ubuntu

Installation

To install use the command line and type:

sudo apt-get install postgresql postgresql-contrib

This will install the latest version available in your Ubuntu release and the commonly used add-ons for it.

Administration

pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line:

sudo apt-get install pgadmin3

Basic Server Setup

To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command.

In a terminal, type:

sudo -u postgres psql postgres

Set a password for the "postgres" database role using the command:

\password postgres

and give your password when prompted. The password text will be hidden from the console for security purposes.

Type Control+D to exit the posgreSQL prompt.

Create database

To create the first database, which we will call "mydb", simply type:

sudo -u postgres createdb mydb

Install Server Instrumentation (for PgAdmin) for Postgresql 8.4 or 9.3

PgAdmin requires the installation of an add-on for full functionality. The "adminpack" addon, which it calls Server Instrumentation, is part of postgresql-contrib, so you must install that package if you haven't already:

sudo apt-get install postgresql-contrib

Then to activate the extension, for ""Postgresql 8.4"", run the adminpack.sql script, simply type:

sudo -u postgres psql < /usr/share/postgresql/8.4/contrib/adminpack.sql

For "Postgresql 9.3"+ install the adminpack "extension" in the "postgres" database:

sudo -u postgres psql
CREATE EXTENSION adminpack;