PostgreSQL 9.2 installation on OEL 6.4
This is my first note in a series of posts about supporting PostgreSQL on OEL 6.4. Let me start with a fair warning, I’m a total newbie when it comes to PostgreSQL (or PG in short). Don’t have any PG database in production, but this might (and I’m sure it will!) change sooner or later — you know what they say that necessity is the mother of invention. Considering economic downturn that’ll likely last to the end of this decade (or even further)…and you find yourself happy that you have at least some alternative.
Actually, I was always fond to an idea to replace some production “big vendor” databases (in particular Oracle, MS SQL and MySQL) with the most sophisticated free & OSS database on the planet. Sure, PG has some “critical” shortcomings (such as complete lack of proper auditing and even worse, the lack of proper incremental binary backup/recovery tools similar to Oracle RMAN), but the robust, feature-rich, MVCC capable RDBMS is more than enough to handle all sorts of application workload without the need to worry about licenses. Let’s start with installation steps for installation of PG 9.2.4 on OEL 6.4:
Let’s check what we already have on OEL 6.4:
[root@acme ~]# uname -r 2.6.39-400.17.1.el6uek.x86_64 [root@acme ~]# yum info postgresql | grep Version Version : 8.4.13
Let’s get rid of PG 8.4.13:
[root@acme ~]# yum erase postgresql // disable OEL 6.4 default PG repository by adding exclude=postgresql* to // /etc/yum/pluginconf.d/rhnplugin.conf [root@acme ~]# nano /etc/yum/pluginconf.d/rhnplugin.conf [root@acme ~]# cat /etc/yum/pluginconf.d/rhnplugin.conf [main] enabled = 0 gpgcheck = 1 exclude=postgresql*
Now we need to install current (PG 9.2) repository:
# rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6.4-x86_64/pgdg-redhat92-9.2-7.noarch.rpm
Now, the installation is as easy as:
# yum install postgresql92-server postgresql92 postgresql92-contrib .... Dependencies Resolved ===============================================... Package Arch ... ===============================================... Installing: postgresql92 x86_64 postgresql92-contrib x86_64 postgresql92-server x86_64 Installing for dependencies: postgresql92-libs x86_64 uuid x86_64 Transaction Summary ===============================================... Install 5 Package(s) Total download size: 5.4 M Installed size: 23 M Is this ok [y/N]: ... [root@acme ~]# psql --version psql (PostgreSQL) 9.2.4
Now, we need to initialize PG cluster and create our first superuser (me;):
// you can't run initdb as root, you must become user under which // postgres server will run! By default the OS user is postgres. [root@acme ~]# su - postgres // cluster initialization. Note that I told initdb my locale, which is // specific to my country. It's important for further // creation of databases in the cluster to specify correct locale!! // Also note, that I could tell initdb where to create data directory, let's // say on some dedicated mount point, such as: // --pgdata=/u01/pg92/data // as this is my test database, I don't mind having data files // in default location (/var/lib/pgsql/9.2/data) -bash-4.1$ /usr/pgsql-9.2/bin/initdb --locale=sl_SI.UTF8 The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "sl_SI.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". fixing permissions on existing directory /var/lib/pgsql/9.2/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in /var/lib/pgsql/9.2/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating collations ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok loading PL/pgSQL server-side language ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/9.2/data or /usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -l logfile start
Now, we need to setup some basic parameters that’ll allow us to connect to the PG over the network:
// // setup static IP for PG "listener" (postmaster) // Obviously make sure that port 5432 is open on firewall! // -bash-4.1$ nano /var/lib/pgsql/9.2/data/postgresql.conf listen_addresses='192.168.1.100' port = 5432 // // edit PG permissions in pg_hba.conf // In my case I limited network access to my 2 workstations. // md5 encrypted passwords are recommended method for login! // -bash-4.1$ nano /var/lib/pgsql/9.2/data/pg_hba.conf host all all 192.168.1.110/24 md5 host all all 192.168.1.120/24 md5
Post installation configuration:
// start PG # service postgresql-9.2 start Starting postgresql-9.2 service: [ OK ] // if you want PG to start on boot... [root@acme ~]# chkconfig | grep postgresql-9.2 postgresql-9.2 0:off 1:off 2:off 3:off 4:off 5:off 6:off # chkconfig --levels 235 postgresql-9.2 on // create superuser... root@acme ~]# su - postgres -bash-4.1$ psql postgres psql (9.2.4) Type "help" for help. postgres=# CREATE ROLE alesk WITH SUPERUSER LOGIN PASSWORD 'mysecret'; CREATE ROLE postgres=#
Posted on 23.04.2013, in Linux, PostgreSQL, etc. and tagged PostgreSQL. Bookmark the permalink. Comments Off on PostgreSQL 9.2 installation on OEL 6.4.