Install PostgreSQL on CentOS 6

Install PostgreSQL on CentOS 6

Hi readers, I recently started playing with Postgresql database on CentOS machine and wanted to share the installation & set up process with you all. In this article I will show you how to install Postgresql on CentOS 6.5 machine.

PostgreSQL

From PostgreSQL site:

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages).

Install PostgreSQL

You can either install the packages provided by the default repositories or go for the Postgresql repository for the latest version. Here I will go with the default respository.

Install the following packages

• postgresql – includes client programs and libraries needed to access a PostgreSQL server
• postgresql-server - main server package which includes programs needed to create and run a postgresql server
• postgresql-contrib – contains contributed packages that are included in the PostgreSQL distribution

Use the following command to install all of them

# yum install postgresql postgresql-server postgresql-contrib

Installing postgresql will create a user and group named postgres. Verify it with the below commands.

# grep postgres /etc/{passwd,group}
/etc/passwd:postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
/etc/group:postgres:x:26:

Initialize Databases

We have got the server installed but before we can start using the server, the default databases needs to be initialized.

Use the following command to initialize the database

# service postgresql initdb

Set up services

By default the postgresql service will be stopped and not enabled. Check this using the below commands.

# chkconfig postgresql --list
postgresql      0:off   1:off   2:off   3:off   4:off   5:off   6:off
# service postgresql status
postmaster is stopped

Start the service and enable it on boot time using the below commands

# service postgresql start
# chkconfig postgresql on
# chkconfig postgresql --list
postgresql      0:off   1:off   2:on   3:on   4:on   5:on   6:off

Verify the service using netstat command as shown below

# netstat -nltp | grep post
tcp   0      0 0.0.0.0:5432     0.0.0.0:*    LISTEN      2022/postmaster
tcp   0      0 :::5432          :::*         LISTEN      2022/postmaster

Lets play with the database

First we will switch to postgres user account to connect to the database.

# su - postgres

Now type psql to connect to the postgresql server

[postgres@cent1: ~] $ psql
psql (8.4.20)
Type "help" for help.

postgres=#

We are now within psql prompt. Lets try some commands.

Create a database:

postgres=# create database demodb;

New database is created and you can list all databases using the \l command. For help type \? to see list of commands.

postgres=# \l

Note: You can also create database from the terminal using createdb command without logging into psql prompt. Check man createdb for more information

Create a user/role:

postgres=# create role testuser;

This will just create a new role but won’t allow login and doesn’t have a password. Check list of users with \du command

postgres=# \du

To create a user with login rights and password set, use below form

postgres=# create role testuser login password 'secret-password';

Note: You can also create a new user from the terminal using the createuser command without logging into psql prompt. Check man createuser for more information

Changing user password

You can use the \password command to change user password.

postgres=# \password testuser
Enter new password:
Enter it again:
postgres=#

Simple tweak

To be able to log into postgresql server using the new user/role that we created earlier, we also need the equivalent unix system account. So you need to create a new user account named testuser.

There is a workaround or alternate way of logging into postgresql server without having system user account. Use the below command instead.

# psql -U postgres -h localhost -W

You will see an error. Don’t panic. There is one more step to do. Open /var/lib/pgsql/data/pg_hba.conf

# vi /var/lib/pgsql/data/pg_hba.conf

Look for the below lines

local   all      all                    ident
host    all      all    127.0.0.1/32    ident
host    all      all    ::1/128         ident

Change ident to trust on all these lines so that it looks like below.

local   all      all                    trust
host    all      all    127.0.0.1/32    trust
host    all      all    ::1/128         trust

Restart the postgresql server

# service postgresql restart

Now you should be able to use the above command to login.

Enable remote connections

By default the postgresql server will listen on 127.0.0.1 and will only accept connections from localhost. To enable remote connections, you need to do few configuration changes.

Step #1: Update postgresql.conf

Find the main configuration file and open it for editing using the below command

# vi $(find / -iname postgresql.conf)

Look for the below line

#listen_addresses = 'localhost'

Uncomment and change it as follows

listen_addresses = '*'

Step #2: Update pg_hba.conf

Find and open this file for editing

# vi $(find / -iname pg_hba.conf)

Append the following line to it. Change the network address range as per your need.

host    all     all     192.168.122.0/24    trust

Step #3: Adjust firewall rules

If you want to access this postgresql server from other machines and you have firewall enabled,  you need to add the following rule to allow connections.

# iptables -I INPUT -m tcp -p tcp --dport 5432 -j ACCEPT

Step #4: Restart services

# service iptables save
# service postgresql restart

Now from any of the client machine you can access this postgresql server using the following command. Note that you need to install postgresql to be able to use psql command on client machines.

# psql -U postgres -h [postgresql-server-ip] -W

That’s it for this tutorial. Thanks for reading.

PostgreSQL Database Backup and Restore Using pg_dump and psql

pg_dump is an effective tool to backup postgres database. It creates a *.sql file with CREATE TABLE, ALTER TABLE, and COPY SQL statements of source database. To restore these dumps psql command is enough.

Using pg_dump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use pg_dump to backup and restore.

For the impatient, here is the quick snippet of how backup and restore postgres database using pg_dump and psql:

Backup:  $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

Restore: $ psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

How To Backup Postgres Database

1. Backup a single postgres database

This example will backup erp database that belongs to user geekstuff, to the file mydb.sql

$ pg_dump -U geekstuff erp -f mydb.sql

It prompts for password, after authentication mydb.sql got created with create table, alter table and copy commands for all the tables in the erp database. Following is a partial output of mydb.sql showing the dump information of employee_details table.

--
-- Name: employee_details; Type: TABLE; Schema: public; Owner: geekstuff; Tablespace:
--

CREATE TABLE employee_details (
employee_name character varying(100),
emp_id integer NOT NULL,
designation character varying(50),
comments text
);

ALTER TABLE public.employee_details OWNER TO geekstuff;

--
-- Data for Name: employee_details; Type: TABLE DATA; Schema: public; Owner: geekstuff
--
COPY employee_details (employee_name, emp_id, designation, comments) FROM stdin;
geekstuff 1001 trainer
ramesh 1002 author
sathiya 1003 reader
\.
--
-- Name: employee_details_pkey; Type: CONSTRAINT; Schema: public; Owner: geekstuff; Tablespace:
--
ALTER TABLE ONLY employee_details

ADD CONSTRAINT employee_details_pkey PRIMARY KEY (emp_id);

2. Backup all postgres databases

To backup all databases, list out all the available databases as shown below.

Login as postgres / psql user:

$ su postgres

List the databases:

$ psql -l

List of databases
Name | Owner | Encoding
-----------+-----------+----------
article | sathiya | UTF8
backup | postgres | UTF8
erp | geekstuff | UTF8
geeker | sathiya | UTF8

Backup all postgres databases using pg_dumpall:

You can backup all the databases using pg_dumpall command.

$ pg_dumpall > all.sql

Verify the backup:

Verify whether all the databases are backed up,

$ grep "^[\]connect" all.sql
\connect article
\connect backup
\connect erp
\connect geeker

3. Backup a specific postgres table

$ pg_dump --table products -U geekstuff article -f onlytable.sql

To backup a specific table, use the –table TABLENAME option in the pg_dump command. If there are same table names in different schema then use the –schema SCHEMANAME option.

How To Restore Postgres Database

1. Restore a postgres database

$ psql -U erp -d erp_devel -f mydb.sql

This restores the dumped database to the erp_devel database.

Restore error messages

While restoring, there may be following errors and warning, which can be ignored.

psql:mydb.sql:13: ERROR:  must be owner of schema public
psql:mydb.sql:34: ERROR:  must be member of role "geekstuff"
psql:mydb.sql:59: WARNING:  no privileges could be revoked
psql:mydb.sql:60: WARNING:  no privileges could be revoked
psql:mydb.sql:61: WARNING:  no privileges were granted
psql:mydb.sql:62: WARNING:  no privileges were granted

2. Backup a local postgres database and restore to remote server using single command:

$ pg_dump dbname | psql -h hostname dbname

The above dumps the local database, and extracts it at the given hostname.

3. Restore all the postgres databases

$ su postgres
$ psql -f alldb.sql

4. Restore a single postgres table

The following psql command installs the product table in the geek stuff database.

$ psql -f producttable.sql geekstuff