Wednesday, 7 August 2013

Centos 6/RHEL install and access Postgresql

PostgreSQL is an Open Source Object-Relational Database Management System and is available from the standard Centos repo.

It's architecture and large array of features make it an attractive solution for many companies who are concerned with data integrity.  

Postgres is an easy-to-install database system and uses a
template system which supports a large part of the SQL standard.
To install it in Centos 6, log in as root and type.

# yum install postgresql postgresql-server


Now enable the database server at boot by typing

# chkconfig postgresql on

Then initialize the database

# service postgresql initdb

Start the database by typing

# service postgresql start

Now assign your current CentOS user account as a database user

Connect to the database using the following command

$ sudo su ­ postgres


A new database will be created by cloning the standard system database so launch the psql command-line utility.

-bash-4.1$ psql template1

Now issue a command to create a database, so by substituting the relevant values with those associated with your system user account

CREATE USER <username> WITH PASSWORD '<password>'

Create your first database, replacing the <database-name> value with something more appropriate

CREATE DATABASE <database-name>

Now complete the user setup by assigning the correct privileges, substituting the relevant values with those used previously


GRANT ALL PRIVILEGES ON DATABASE <database-name> to <username>


When finished, quit by typing


 \q


-bash-4.1$ exit
exit 


Changing logging parameters

You can alter the logging parameters to customise the recorded values.

$ sudo vi /var/lib/pgsql/data/postgresql.conf

Scroll down and find the following line

log_line_prefix


Now uncomment and change this line to read


log_line_prefix = '%d %u %t'


This will use the database name, username, and timestamp format when writing the log files,

Save the file and restart the database server

$ sudo service postgresql restart


Connecting to Postgresql

Connecting to Postgresql is different to connecting to MySql,
to access to access the database, first issue the folowing

$ sudo so postgres

Now access the interactive screen by typing

psql template1

-bash-4.1$ psql template1

Password:

psql (8.4.13)


Type "help" for help.


template1=#


From here you can use SQL to complete any template related task, you can quit the terminal by typing

\q


This command will return you to the postgres user prompt, which you can close at any time by issuing the following command


exit


Accessing a specific database as a specific user

If you wish to access a specific database as a specific user you would begin by accessing the main terminal as postgresql user

$ sudo su ­ postgres

Having done this you would access the relevant database by using the appropriate user in the following way


psql -d <database-name> -U <username> ­W


Complete this process by submitting your password when requested 


The entire process may look similar to below


$ sudo su - postgres


-bash-4.1$ psql -d <database-name> -U <username> -W


Password for user <username>


psql (8.4.13)


Type "help" for help


<database-name>=>


Creating a copy of a database in PostgreSQL
  
You can use any existing database on the server as a template when creating a new database.

To do this, simply access the psql console as postgres user, and issue the following command.


CREATE DATABASE <new-database-name> WITH TEMPLATE <original-database-name> OWNER <username>;

The original database needs to be idle in order for this command to work properly.
 




See the PostgreSql site for more