At the end of this walkthrough you will have the PostgreSQL 8.4 database installed on CentOS 6.2 ready for use with your web projects. Postgres 8.4 is not the latest version, but it is stable and good enough for web development purposes. This set-up is “webby” in the sense that the it should be familiar to web developers.
Prerequisites
You need to be familiar with basic Linux system administration including editing configuration files with text-editors like vi or emacs.
This is our system. It is a basic CentOS 6.2 installation with a static IP:
$ uname -a Linux schettino.kelvinwong.ca 2.6.32-220.4.1.el6.i686 #1 SMP Mon Jan 23 22:37:12 GMT 2012 i686 i686 i386 GNU/Linux $ cat /etc/redhat-release CentOS release 6.2 (Final)
Install Postgres
Installation of Postgres with yum is simple:
[kelvin@schettino ~]$ sudo yum install postgresql-server
[sudo] password for kelvin:
Loaded plugins: fastestmirror, presto
Loading mirror speeds from cached hostfile
* base: mirror.its.sfu.ca
* extras: mirror.its.sfu.ca
* updates: mirror.its.sfu.ca
base | 3.7 kB 00:00
extras | 3.5 kB 00:00
updates | 3.5 kB 00:00
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql-server.i686 0:8.4.9-1.el6_1.1 will be installed
--> Processing Dependency: postgresql-libs(x86-32) = 8.4.9-1.el6_1.1 for package: postgresql-server-8.4.9-1.el6_1.1.i686
--> Processing Dependency: postgresql(x86-32) = 8.4.9-1.el6_1.1 for package: postgresql-server-8.4.9-1.el6_1.1.i686
--> Processing Dependency: libpq.so.5 for package: postgresql-server-8.4.9-1.el6_1.1.i686
--> Running transaction check
---> Package postgresql.i686 0:8.4.9-1.el6_1.1 will be installed
---> Package postgresql-libs.i686 0:8.4.9-1.el6_1.1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
postgresql-server i686 8.4.9-1.el6_1.1 base 3.3 M
Installing for dependencies:
postgresql i686 8.4.9-1.el6_1.1 base 2.7 M
postgresql-libs i686 8.4.9-1.el6_1.1 base 201 k
Transaction Summary
================================================================================
Install 3 Package(s)
Total download size: 6.2 M
Installed size: 28 M
Is this ok [y/N]: y
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 6.2 M
(1/3): postgresql-8.4.9-1.el6_1.1.i686.rpm | 2.7 MB 00:01
(2/3): postgresql-libs-8.4.9-1.el6_1.1.i686.rpm | 201 kB 00:00
(3/3): postgresql-server-8.4.9-1.el6_1.1.i686.rpm | 3.3 MB 00:01
--------------------------------------------------------------------------------
Total 1.5 MB/s | 6.2 MB 00:04
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : postgresql-libs-8.4.9-1.el6_1.1.i686 1/3
Installing : postgresql-8.4.9-1.el6_1.1.i686 2/3
Installing : postgresql-server-8.4.9-1.el6_1.1.i686 3/3
Installed:
postgresql-server.i686 0:8.4.9-1.el6_1.1
Dependency Installed:
postgresql.i686 0:8.4.9-1.el6_1.1 postgresql-libs.i686 0:8.4.9-1.el6_1.1
Complete!
[kelvin@schettino ~]$
The server is installed along with the required client programs.
Configure Postgres – Initialize and start service
After installing Postgres you will need to initialize the database (once only):
[kelvin@schettino ~]$ sudo service postgresql initdb
Initializing database: [ OK ]
Set the server to restart on reboots and start the postmaster service:
[kelvin@schettino ~]$ sudo chkconfig postgresql on
[sudo] password for kelvin:
[kelvin@schettino ~]$ sudo service postgresql start
Starting postgresql service: [ OK ]
Configure Postgres – Set superuser password
Now let’s set a password for the superuser (the postgres user) using the PostgreSQL interactive terminal. Jump into the postgres user by using su (with the dash to get a login shell):
[kelvin@schettino ~]$ su - Password: [root@schettino ~]# su - postgres -bash-4.1$ psql psql (8.4.9) Type "help" for help. postgres=# \password postgres Enter new password: Enter it again: postgres=# \q -bash-4.1$
Configure Postgres – Activate password authentication
By default, the server uses ident as defined in the “PostgreSQL Client Authentication Configuration File”. If you open up pg_hba.conf you can see this default configuration:
67 68 69 70 71 72 73 74 | # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident # IPv4 local connections: host all all 127.0.0.1/32 ident # IPv6 local connections: host all all ::1/128 ident |
Ident is a mapping of local system users (see /etc/passwd for list of system users) to Postgres users. I have never found this authentication method useful for any of the web development work that I have done. I always change it to “md5” which allows you to create arbitrary users and passwords. Let’s change the server’s client configuration file (I assume you are still using the postgres user shell):
-bash-4.1$ whoami postgres -bash-4.1$ vim /var/lib/pgsql/data/pg_hba.conf
Change the “ident” methods to “md5” methods at the bottom of the pg_hba.conf file:
67 68 69 70 71 72 73 74 75 76 77 | # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # If you don't want to open Postgres to the Internet # don't enable this line host all all 0.0.0.0/0 md5 |
By default, Postgres binds only to localhost and you will need to explicitly tell it to bind to your machine’s IP address. The setting is in postgres.conf. If you don’t need remote access you can skip this.
-bash-4.1$ vim /var/lib/pgsql/data/postgresql.conf
Change the listen_addresses setting to an asterisk to listen to all available IP addresses:
57 58 59 60 61 62 63 | # - Connection Settings - listen_addresses = '*' #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all # (change requires restart) |
Restart your postgres server (exit postgres user into the root shell):
-bash-4.1$ exit logout [root@schettino ~]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ] [root@schettino ~]#
Open Firewall (optional)
If you want remote access to the server on Postgres port 5432 you will have to open a port on the firewall. If you still are the root user, type the following:
[root@schettino ~]# whoami root [root@schettino ~]# vim /etc/sysconfig/iptables
You can just copy the SSH port rule in iptables and modify the port number from 22 to 5432. Add the following rule just below the SSH port rule and above the rejection rule for the INPUT chain:
10 | -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT |
When changed, it should look like this:
2 3 4 5 6 7 8 9 10 11 12 13 | *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT |
Reload the rules:
[root@schettino ~]# service iptables restart iptables: Flushing firewall rules: [ OK ] iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Unloading modules: [ OK ] iptables: Applying firewall rules: [ OK ] [root@schettino ~]# exit logout [kelvin@schettino ~]$
Try it out with pgbench (optional)
To demonstrate the basic use of your new Postgres server, you can try out pgbench which is in the postgresql-contrib RPM. Let’s install it, create a new user, create a new database and run pgbench against it:
[kelvin@schettino ~]$ sudo yum install postgresql-contrib Loaded plugins: fastestmirror, presto Loading mirror speeds from cached hostfile * base: mirror.its.sfu.ca * extras: mirror.its.sfu.ca * updates: mirror.its.sfu.ca Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package postgresql-contrib.i686 0:8.4.9-1.el6_1.1 will be installed --> Processing Dependency: libxslt.so.1(LIBXML2_1.0.18) for package: postgresql-contrib-8.4.9-1.el6_1.1.i686 --> Processing Dependency: libxslt.so.1(LIBXML2_1.0.11) for package: postgresql-contrib-8.4.9-1.el6_1.1.i686 --> Processing Dependency: libxslt.so.1 for package: postgresql-contrib-8.4.9-1.el6_1.1.i686 --> Processing Dependency: libossp-uuid.so.16 for package: postgresql-contrib-8.4.9-1.el6_1.1.i686 --> Running transaction check ---> Package libxslt.i686 0:1.1.26-2.el6 will be installed ---> Package uuid.i686 0:1.6.1-10.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: postgresql-contrib i686 8.4.9-1.el6_1.1 base 346 k Installing for dependencies: libxslt i686 1.1.26-2.el6 base 448 k uuid i686 1.6.1-10.el6 base 54 k Transaction Summary ================================================================================ Install 3 Package(s) Total download size: 848 k Installed size: 3.3 M Is this ok [y/N]: y Downloading Packages: Setting up and reading Presto delta metadata Processing delta metadata Package(s) data still to download: 848 k (1/3): libxslt-1.1.26-2.el6.i686.rpm | 448 kB 00:00 (2/3): postgresql-contrib-8.4.9-1.el6_1.1.i686.rpm | 346 kB 00:00 (3/3): uuid-1.6.1-10.el6.i686.rpm | 54 kB 00:00 -------------------------------------------------------------------------------- Total 520 kB/s | 848 kB 00:01 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : uuid-1.6.1-10.el6.i686 1/3 Installing : libxslt-1.1.26-2.el6.i686 2/3 Installing : postgresql-contrib-8.4.9-1.el6_1.1.i686 3/3 Installed: postgresql-contrib.i686 0:8.4.9-1.el6_1.1 Dependency Installed: libxslt.i686 0:1.1.26-2.el6 uuid.i686 0:1.6.1-10.el6 Complete! [kelvin@schettino ~]$ which pgbench /usr/bin/pgbench
Create a new Postgres user by using the createuser wrapper (the P switch allows you to set a password for your new user):
[kelvin@schettino ~]$ su - Password: [root@schettino ~]# su - postgres -bash-4.1$ createuser -P francesco Enter password for new role: [password for user francesco] Enter it again: Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n Password: [password for postgres] -bash-4.1$
Make a new database named “winnings” and change the owner to “francesco”:
-bash-4.1$ createdb -O francesco winnings Password: [password for postgres] -bash-4.1$
Now we can fill it up with pgbench:
-bash-4.1$ pgbench -i -U francesco winnings Password: [password for user francesco] NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping creating tables... 10000 tuples done. 20000 tuples done. 30000 tuples done. 40000 tuples done. 50000 tuples done. 60000 tuples done. 70000 tuples done. 80000 tuples done. 90000 tuples done. 100000 tuples done. set primary key... NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches" NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers" NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts" vacuum...done. -bash-4.1$ pgbench -c 4 -S -t 2000 -U francesco winnings Password: [password for user francesco] starting vacuum...end. transaction type: SELECT only scaling factor: 1 query mode: simple number of clients: 4 number of transactions per client: 2000 number of transactions actually processed: 8000/8000 tps = 4836.016718 (including connections establishing) tps = 5052.773057 (excluding connections establishing) -bash-4.1$ pgbench -c 4 -t 2000 -U francesco winnings Password: [password for user francesco] starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 query mode: simple number of clients: 4 number of transactions per client: 2000 number of transactions actually processed: 8000/8000 tps = 237.345234 (including connections establishing) tps = 237.889294 (excluding connections establishing) -bash-4.1$
You can clean up the database by dropping the “winnings” database and dropping “francesco”:
-bash-4.1$ dropdb winnings Password: [password for postgres] -bash-4.1$ dropuser francesco Password: [password for postgres] -bash-4.1$
Enjoy your webby Postgres!
Caveat! If you have an Apache/PHP5 server that wants to talk to your Postgres, you will have to set the appropriate SELinux boolean to allow the communication: setsebool -P httpd_can_network_connect_db 1