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 installing will be created ‘postgres” system user as default admin user. So we need to switch ‘postgres” 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. create a database for a sigle 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=#
a. Create a superuser user:
Create user mysuper with password ’1234′ SUPERUSER
create user mysuper with password ’1234′ SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION;
b.Change Database Owner:
alter database database_name owner to new_owner;
Revoking database privilege.
REVOKE ALL PRIVILEGES ON testdb FROM liju;
d. 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
e. 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 ~]#
f. 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
g. 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 ~]#
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
h. 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,
-Njoy !!!!
Author: Liju Mathew
Visit Liju's Website - Email Liju
I'm Liju, one linux enthusiastic who have been playing with Linux for more than 7 years. I'm curious about to read blog, learning and implementing new technologies from my personal experience. Like to be play with burning head on busy schedule :-) This is a bookmark of all challenges that I'd faced which would be helpful to others sometimes as I'd learn it from the same way :-)
Nothing more, I have to go miles, before I sleep