CentOS PostgreSQL Installation tutorial
CentOS PostgreSQL
centos postgresql :
PostgreSQL is a powerful, open source object-relational database system.
In the following tutorial I’ll show how to install PostgreSQL on your CentOS box
CentOS PostgreSQL Installation
We can install PostgreSQL in (at-least) two ways:
- Using YUM
- Compile from source
Install from repository
yum install postgresql-server
- this will install the package postgresql-server, also: postgresql and postgresql-libs.
-
centos postgresql
Install from source
If you want to install the latest version of PostgreSQL you should compile from source. it’s recommended for advanced users and one may argue it’s recommended too for production.
Anyway, this article from DigitalOcean covers this area well (and more). If you want to compile using source you better move to that article. If you prefer or installed using repository (yum), continue…
PostgreSQL Service
if you’ll try to start PostgreSQL using the service command, you will see an error tells you must init the db first and create the db files in: /var/lib//pgsql/data
so,
to init on centos postgresql service use:
service postgresql initdb
This created a data folder in /var/lib/pgsql. You can’t run this command again without deleting first this folder (and all your data).
Also, when you called the initdb command above from RedHat’s init script configured permissions on the database. These configuration settings are in the pg_hba.conf file inside the data folder.
By default all permissions are ‘Ident’,
means the only user that can get in initially is user “postgres”, so if you’ll try ‘psql’ from root you’ll get error:
psql: FATAL: Ident authentication failed for user “root”
If you want to login and use postgres with other users than `postgres` you can change the permissions method in pg_hba.conf. change from ‘ident’ to ‘md5’ is recommended.
If you want to use phpPgAdmin (described later) you should change from ‘Ident’ to ‘md5’ or else it won’t login to your system.
Set port and Listen Addresses
If you need to change the default port (5432 by default) and Listen Addresses (localhost by default), you can set those vars inside the postgresql.conf inside /var/lib/pgsql/data folder.
#listen_addresses = 'localhost' #port = 5432
Start service
and then, to start on centos postgresql service use:
service postgresql start
to make centos postgresql load on boot use the chkconfig command as follows:
chkconfig postgresql on
and That’s it!
What next?
- read the PostgreSQL: Documentation: Getting Started to configure your environment.
Managing from Command line
login to postgres
As I mentioned, default setup has ident authentication means the only user that can get in initially is user “postgres”, so if you haven’t changed permissions scheme you should su to postgres before.
to start ‘psql’ as postgres:
# change user to postgres su - postgres # start psql manager psql # CTRL + D twice to exit both psql and su. # You can also short the two commands into: # su postgres -c psql
Add (or create) a user with permission to specific database?
Read this great tutorial.
PhpPgAdmin
PostgreSQL visual interface similar to phpMyAdmin? – in short, if you know phpMyAdmin and want phpPgAdmin, you need to add the EPEL repositories, Apache (yum install httpd) and then install using:
If your permissions scheme is currently ‘Ident’ you might need to change that to ‘md5’ as PhpPgAdmin requires it.
yum install phpPgAdmin
Then visit in your browser: http://localhost/phpPgAdmin
- centos phppgadmin
Remote connection
Edit /etc/httpd/conf.d/phpPgAdmin.conf if you want to allow access remotly and restart httpd (service httpd restart).
do you use pgsql, postgres, root, administrator as login or even user without password?
if you do, set the $conf[‘extra_login_security’] entry to false in your et/phpPgAdmin/config.inc.php.
Change default Postgres user password
If you really want to use the “postgres” role, make sure you set it up a password and $conf[‘extra_login_security’] is false.
use the command:
passwd postgres
to change the system user password and
ALTER USER Postgres WITH PASSWORD 'password';
That alters the password for within the database. To change the password inside Postgresql. there is also short code (inside psql):
\password
Which will ask from you a new password to set.
Cheers!
