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
Labels: Centos 6, install & access Postgresql, RHEL