Activities

October 2013
M T W T F S S
« Sep   Nov »
 123456
78910111213
14151617181920
21222324252627
28293031  

PostgreSQL installation, Configuration and Administration

PostgreSQL is an open source database released under the PostgreSQL license. This license allows for the free use and distribution of the PostgreSQL software for any reason. After few years back, I got a chance to setup a PostgreSQL server (one enterprise-class open source database) for an upcoming Rails project. I had worked with PostgreSQL7 few years back. The latest version is 9.3 and EnterpriseDB company currently take over the PostgreSQL distribution. I found postgreSQL is good for developing desktop type or Intranet application as the complexity of it’s design and MYSQL is good for web based project due to it’s high performance on light weight platform.

How Do I install latest PostgreSQL on CentOS server.

You can easily get the repository file from Postgresql Yum repository. Yum rpms can be downloaded from here.

[root@rc-090 installation]# wget http://yum.postgresql.org/9.3/redhat/rhel-6-i386/pgdg-centos93-9.3-1.noarch.rpm
[root@rc-090 installation]# rpm -ivh pgdg-centos93-9.3-1.noarch.rpm
warning: pgdg-centos93-9.3-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                ########################################### [100%]
   1:pgdg-centos93          ########################################### [100%]
[root@rc-090 installation]#

The real problem is you can not install easily using “yum install postgresql” as each of it distribution having unique release name. How do I find the correct postgresql version which support yum install.

Check the file “/etc/yum.repos.d/pgdg-93-centos.repo” which shows the version in the file name itself. 93 ie 9.3 and also we can verify the same by browsing the base url on browser ie http://yum.postgresql.org/9.3/redhat/. Here you can see various PG distribution according to our OS platform. Open the proper folder and search the string “postgresql” and identify the package name respect to the YUM install.

[root@rc-090 installation]# yum install postgresql93-server postgresql93-libs postgresql93-contrib
Dependencies Resolved
=======================================================================================================================
 Package                             Arch                Version                           Repository             Size
=======================================================================================================================
Installing:
 postgresql93-contrib                i686                9.3.0-1PGDG.rhel6                 pgdg93                477 k
 postgresql93-libs                   i686                9.3.0-1PGDG.rhel6                 pgdg93                193 k
 postgresql93-server                 i686                9.3.0-1PGDG.rhel6                 pgdg93                4.0 M
Installing for dependencies:
 postgresql93                        i686                9.3.0-1PGDG.rhel6                 pgdg93                1.0 M
 uuid                                i686                1.6.1-10.el6                      base                   54 k
Transaction Summary
=======================================================================================================================
Install       5 Package(s)
Total download size: 5.7 M
Installed size: 23 M
Is this ok [y/N]:
[root@rc-090 installation]# service postgresql-9.3 initdb
Initializing database:                                     [  OK  ]
[root@rc-090 installation]#
[root@rc-090 installation]# service postgresql-9.3 start
Starting postgresql-9.3 service:                           [  OK  ]
[root@rc-090 installation]#

Now you have PostgreSQL successfully installed. Next I’m going to,

a. create a postgresql user and password
b. create a database for the above user.
c. Permitting all the access to db for a particular user.
d. Restoring database.
e. Backup the database.
f. Setting UP PgMyADmin (web UI like PhpMyadmin) to manage tables.
g. Publishing postgresSQL server.

a. Creating User

The default installation will create ‘postgres” system user as default admin user. So we need to switch ‘postgres” (su – postgres from command line)system user shell account to create other users.

a. Creating postgres user

Switch to postgres system account and type “psql” to get the db console. Then execute the following command “CREATE USER liju WITH password ‘pass123’;” will create a user “liju” with password “pass123”

[root@rc-090 installation]# su - postgres
-bash-4.1$ psql
psql (8.4.13, server 9.3.0)
WARNING: psql version 8.4, server version 9.3.
         Some psql features might not work.
Type "help" for help.
postgres=# CREATE USER liju WITH password 'pass123';
CREATE ROLE
postgres=#

b. creating database for a single user.

Command : CREATE DATABASE WITH OWNER ;

 postgres=# CREATE DATABASE testdb WITH OWNER liju;
CREATE DATABASE
postgres=#

c. Permitting all the access to db for a particular user

Command : GRANT ALL PRIVILEGES ON DATABASE to

postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb to liju;
GRANT
postgres=#

D. Create a superuser user:
Create user mysuper with password ‘1234’ SUPERUSER

psql#create user mysuper with password '1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION;

E. Change Database Owner:

psql# alter database database_name owner to new_owner;

F.Revoking database privilege.

psql# REVOKE ALL PRIVILEGES ON testdb FROM liju;

G. How to change PostgreSQL user password ?

psql# ALTER USER username WITH PASSWORD 'newpassword';

H. How do I verify my user name and password are working.

In order to verify postgres user credential, you may need to create system user exactly same as postgres user. Otherwise you need to test it over PgMyAdmin.

[root@rc-090 installation]# psql -h 127.0.0.1 -d testdb -U liju -W
Password for user liju:
psql: FATAL:  Ident authentication failed for user "liju"
[root@rc-090 installation]#

Solution:
Postgres using environment setting (IDENT) to identify the REAL user by default. So that you can not login to psql shell even though user details are correct. We need to change the user authentication ident to password in postgresql configuration file pg_hba.conf) . This file can be found under “$PGDATA” or in “/var/lib/pgsql/9.3/data/”

Now I changed ident mode to password on /var/lib/pgsql/9.3/data/pg_hba.conf which would apply all the calls to loopback IP.

 host    all             all             127.0.0.1/32            ident

to

host    all             all             127.0.0.1/32            password

Restarted Postgresql and now I could able to login to users psql console.

[root@rc-090 installation]# /etc/init.d/postgresql-9.3 restart
Stopping postgresql-9.3 service:                           [  OK  ]
Starting postgresql-9.3 service:                           [  OK  ]
[root@rc-090 installation]# psql -h 127.0.0.1 -d testdb -U liju -W
Password for user liju:
psql (8.4.13, server 9.3.0)
WARNING: psql version 8.4, server version 9.3.
         Some psql features might not work.
Type "help" for help.
testdb=>

🙂 🙂 a tips !!!

Also I have added another line to have LAN access enabled for all postgresql users.

host    all             all             192.168.0.0/24           password

Access remote Postgres database : psql -h 192.168.0.90 -U liju -W -d testdb

I. Configuring postgresSQL listen to all IP and enabling networking.

You may need to edit the config file “postgresql.conf” from “/var/lib/pgsql/9.3/data/postgresql.conf” and listen_addresses to “*” and declare the port 5432 for postgres daemon and then restart the service.

vi /var/lib/pgsql/9.3/data/postgresql.conf
# - Connection Settings -
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
[root@rc-090 ~]# netstat -nlp | grep ":5432"
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      8887/postmaster
tcp        0      0 :::5432                     :::*                        LISTEN      8887/postmaster
[root@rc-090 ~]#

J. Listing all the databases.

You may use the switch “\l” to list database or psql -l. “\q” to quit the shell.

[root@rc-090 installation]# psql -h 127.0.0.1 -d testdb -U liju -W
Password for user liju:
testdb=> \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 testdb    | liju     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/liju
                                                             : liju=CTc/liju

Listing all users

Use the switch ” \du” to list all the users.

postgres=# \du
            List of roles
 Role name | Attributes  | Member of
-----------+-------------+-----------
 censususr | Superuser   | {}
           : Create role
           : Create DB
 liju      |             | {}
 postgres  | Superuser   | {}
           : Create role
           : Create DB

K. How To Backup Postgres Database,

1. Backup a single database

Command : pg_dump -d [source_db] -h [host] -U [user-name] -f [dumpfilename.sql]

[root@rc-040 ~]# pg_dump -d censusdb -h 127.0.0.1  -U censususr -W -f censusdb_backup.sql
Password:
[root@rc-040 ~]# du -sh censusdb_backup.sql                                     1.6M    censusdb_backup.sql
[root@rc-040 ~]#

Backup and restoring data on Postgresql9.3.4

Recently I had to take a postresql backup from Amazon RDS server using pg_dump command and but I can not restore it using psql command as it is showing “invalid command \N” during the time.

psql:ibcmrat-qa.sql:6308: invalid command \N
psql:ibcmrat-qa.sql:6309: invalid command \N
psql:ibcmrat-qa.sql:6310: invalid command \N
psql:ibcmrat-qa.sql:6311: invalid command \N

Solution
For backup

#pg_dump -i -h hostname  -p 5432 -U username -F c -W -b -v -f my_dbname_backup.sql my_dbname

For restore

#pg_restore -i -h hostname -p 5432 -U username -d mydb_name -v "my_dbname_backup.sql"

2. Backup all databases

For backuping full databas, we need to login to postgres user shell and execute pg_dumpall command.

 -bash-3.2$ pg_dumpall > alldb.sql
-bash-3.2$

3. Backing up only a table from database.

pg_dump --table users -U liju testdb -f users.testdb.sql

L. How to restore backup

You can use psql command to restore the database easily. You may need to create same user account on destination server to have a successful restoration.

Command : psql -d [database name] -h [hostname] -U [user name] -W -f [backupfile.sql]

Here I’m showing backup a database censusdb from local server and restore it on testdb in a remote server 192.168.0.90.

At remote server
Also I have created same user name and given full privileges to this user at remote server.

 [root@rc-090 ~]# su - postgres
-bash-4.1$ createdb testdb
-bash-4.1$ createuser censususr
Shall the new role be a superuser? (y/n) y
-bash-4.1$ psql
psql (8.4.13, server 9.3.0)
WARNING: psql version 8.4, server version 9.3.
         Some psql features might not work.
Type "help" for help.
postgres=# ALTER USER censususr WITH PASSWORD 'test123';
ALTER ROLE

At local database server

[root@rc-040 ~]# pg_dump -h 127.0.0.1  -d  censusdb -U censususr -W -f censususr.sql
Password:
[root@rc-040 ~]# psql -h 192.168.0.90 -U censususr  -W -d testdb -f censususr.sql
Password for user censususr:
SET
SET
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE

i. How to setup web based Postgres admin tool.

phpPgAdmin is a web-based administration tool for PostgreSQL. It is perfect for PostgreSQL DBAs, newbies, and hosting services. We can easily install on any Php based hosting but it required the php-pgsql extension enabled on server.

Download PgAdmin from http://phppgadmin.sourceforge.net/ and extract it on any web location.

#wget http://downloads.sourceforge.net/project/phppgadmin/phpPgAdmin%20%5Bstable%5D/phpPgAdmin-5.1/phpPgAdmin-5.1.tar.gz
#tar -zxvf  phpPgAdmin-5.1.tar.gz
# mv phpPgAdmin-5.1 pgadmin
#cd pgadmin/conf
# cp config.inc.php-dist config.inc.php

Then edit config.inc.php and update the lines to as shown below,

host = ‘[postgres server ip]’ and extra_login_security = “false” Pls note you need to set password to root postgres account using the psql command “ALTER USER postgres WITH PASSWORD ‘password’;

One you have set everything, you will see the following screen,

postgres

-Njoy !!!! 🙂

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>