Ref:
http://www.cyberciti.biz/faq/howto-fedora-linux-install-postgresql-server/
http://www.unixmen.com/postgresql-9-4-released-install-centos-7/
http://tecadmin.net/install-postgresql-on-centos-rhel-and-fedora/
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-a-centos-vps
====================================================================================================
Installing PostgreSQL 9.4 And phpPgAdmin In CentOS 7/6.5/6.4
Introduction:
PostgreSQL is an open source object-relational, highly scalable, SQL compliant database management system. PostgreSQL is developed at the University of California at Berkeley Computer Science Department. This article will help you for installing PostgreSQL on CentOS, RHEL and Fedora Systems.
PostgreSQL is a powerful, open-source object-relational database system. It runs under all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS, Solaris, Tru64), and Windows OS.
PostgreSQL 9.4 has been released last week with major enhancements, fixes, and features. Read what is new in PostgreSQL 9.4.
http://www.postgresql.org/docs/9.4/static/release-9-4.html
==================================================================================================================
Install PostgreSQL:
Update the repository list using command:
# yum update
Go to the PostgreSQL repository download page, and add the PostgreSQL 9.4 repository depending upon your server architecture.
Repo Download Page: http://yum.postgresql.org/repopackages.php
For CentOS 6.x 32bit:
# rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-i386/pgdg-centos94-9.4-1.noarch.rpm
For CentOS 6.x 64bit:
# rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm
For CentOS 7 64bit:
# rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
============================================================================================
Now, Install postgresql with the following command:
# yum install postgresql94-server postgresql94-contrib
OR
# yum install postgresql94-server postgresql94
Initializing PGDATA :
After installing PostgreSQL server, It’s required to initialize it before using first time. To initialize database use below command.
Initialize postgresql database using following command : On CentOS 6.x systems:
# service postgresql-9.4 initdb
On CentOS 7 systems:
# /usr/pgsql-9.4/bin/postgresql94-setup initdb
Above command will take some time to initialize PostgreSQL first time. PGDATA environment variable contains the path of data directory.
PostgreSQL data directory Path: /var/lib/pgsql/9.4/data/
==================================================================
Start PostgreSQL Server
Then, start postgresql service and make it to start automatically on every reboot.
On CentOS 6.x systems:
# service postgresql-9.4 start
# chkconfig postgresql-9.4 on
On CentOS 7 systems:
systemctl enable postgresql-9.4
systemctl start postgresql-9.4
===================================
Verify PostgreSQL Installation
After completing above steps, you have installed PostgreSQL 9.4 on your server, Let’s login to postfix to verify that installation completed successfully.
# su - postgres
Use psql command to access PostgreSQL prompt with admin privileges.
$ psql
psql (9.4.1)
Type "help" for help.
postgres=#
You may create password for user postgres for security purpose.
postgres=# \password postgres
Congratulation’s! You have successfully installed PostgreSQL Server.
==============================================================================
Adjust Iptables/Firewall
Next, adjust iptables to access postgresql from remote systems.
On CentOS 6.x systems:
vi /etc/sysconfig/iptables
Add the following line:
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
Save and exit the file. Restart iptables service.
service iptables restart
On CentOS 7 systems:
firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --permanent --add-port=80/tcp
firewall-cmd --reload
==============================================================================
Adjust SELinux
Run the following command to make PostgreSQL work if SELinux enabled on your system.
setsebool -P httpd_can_network_connect_db 1
==================================================================================
You may not login to PostegreSQL if you didn’t run the above command.
Access PostgreSQL command prompt
The default database name and database user are “postgres”. Switch to postgres user to perform postgresql related operations:
su - postgres
To login to postgresql, enter the command:
psql
Sample Output:
psql (9.4.0)
Type "help" for help.
postgres=#
To exit from posgresql prompt, type \q following by quit to return back to the Terminal.
Set “postgres” user password
Login to postgresql prompt,
su - postgres
psql
.. and set postgres password with following command:
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q
To install PostgreSQL Adminpack, enter the command in postgresql prompt:
postgres=# CREATE EXTENSION adminpack;
CREATE EXTENSION
Create New User and Database
For example, let us create a new user called “senthil” with password “centos”, and database called “mydb”.
Switch to postgres user:
su - postgres
Create user senthil.
$ createuser senthil
Create database:
$ createdb mydb
Now, login to the psql prompt, and set password and Grant access to the database mydb for senthil:
$ psql
psql (9.4.0)
Type "help" for help.
postgres=# alter user senthil with encrypted password 'centos';
ALTER ROLE
postgres=# grant all privileges on database mydb to senthil;
GRANT
postgres=#
Delete Users and Databases
To delete the database, switch to postgres user:
su - postgres
Enter command:
$ dropdb <database-name>
To delete a user, enter the following command:
$ dropuser <user-name>
Configure PostgreSQL-MD5 Authentication
MD5 authentication requires the client to supply an MD5-encrypted password for authentication. To do that, edit /var/lib/pgsql/9.4/data/pg_hba.conf file:
vi /var/lib/pgsql/9.4/data/pg_hba.conf
Add or Modify the lines as shown below
[...]
# TYPE DATABASE USER 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
host all all 192.168.1.0/24 md5
# IPv6 local connections:
host all all ::1/128 md5
[...]
Restart postgresql service to apply the changes:
On CentOS 6.x systems:
service postgresql-9.4 restart
On CentOS 7 systems:
systemctl restart postgresql-9.4
Configure PostgreSQL-Configure TCP/IP
By default, TCP/IP connection is disabled, so that the users from another computers can’t access postgresql. To allow to connect users from another computers, Edit file /var/lib/pgsql/9.4/data/postgresql.conf:
vi /var/lib/pgsql/9.4/data/postgresql.conf
Find the lines:
[...]
#listen_addresses = 'localhost'
[...]
#port = 5432
[...]
Uncomment both lines, and set the IP address of your postgresql server or set ‘*’ to listen from all clients as shown below:
listen_addresses = '*'
port = 5432
Restart postgresql service to save changes:
On CentOS 6.x systems:
/etc/init.d/postgresql-9.4 restart
On CentOS 7 systems:
systemctl restart postgresql-9.4
==============================================================================================
How to Install phpPgAdmin on CentOS using Yum :
phpPgAdmin is web based client written in php for accessing and managing PostgreSQL databases. It provides very efficient way to work with databases on PostgreSQL like, create database, table, alter database, export/import database etc.
This article will you to install phpPgAdmin on CentOS/RedHat System. It required Apache and PostgreSQL to be installed on server. If you do not have them read our turtorials to how to install them.
Manage PostgreSQL with phpPgAdmin
phpPgAdmin is a web-based administration utility written in PHP for managing PosgreSQL.
phpPgAdmin is available only in PostgreSQL RPM repository. If you didn’t add PostgreSQL repository, you should add EPEL repository.
Follow the below link to install EPEL repository on CentOS 6.x. [http://www.unixmen.com/install-epel-repository-rhel-centos-scientific-linux-6/]
Install EPEL Repository On RHEL / CentOS / Scientific linux 6.x, 5.x:
# wget http://epel.mirror.net.in/epel/6/i386/epel-release-6-8.noarch.rpm
# rpm -Uvh epel-release-6-8.noarch.rpm
CentOS/RHEL 5, 32-Bit:
# wget http://yum.postgresql.org/9.1/redhat/rhel-5-i386/pgdg-centos91-9.1-4.noarch.rpm
CentOS/RHEL 6, 32-Bit:
# wget http://yum.postgresql.org/9.1/redhat/rhel-6-i386/pgdg-centos91-9.1-4.noarch.rpm
CentOS/RHEL 5, 64-Bit:
# wget http://yum.postgresql.org/9.1/redhat/rhel-5.0-x86_64//pgdg-centos91-9.1-4.noarch.rpm
CentOS/RHEL 6, 64-Bit:
# wget http://yum.postgresql.org/9.1/redhat/rhel-6.3-x86_64/pgdg-centos91-9.1-4.noarch.rpm
Now list out the installed repositories using command:
# yum repolist
For CentOS 7, refer the following link.
http://www.unixmen.com/install-epel-repository-centos-rhel-7/
[root@localhost ~]# cat /etc/centos-release
CentOS Linux release 7 (Broken)
Download EPEL and install using yum:
yum install http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
Or,
yum install epel-release
Update the repository using command:
yum update
==============================================================================
Step 2: Install phpPgAdmin using Yum
Install phpPgAdmin package using yum command line tool.
# yum install phpPgAdmin httpd
Note: phpPgAdmin is case sensitive. Use upper and lower cases properly as shown in the above command.
By default, you can access phppgadmin using http://localhost/phpPgAdmin from your local system only. To access remote systems, do the following.
Edit file /etc/httpd/conf.d/phpPgAdmin.conf:
vi /etc/httpd/conf.d/phpPgAdmin.conf
Make the changes as shown below in the bold letters.
[...]
Alias /phpPgAdmin /usr/share/phpPgAdmin
<Location /phpPgAdmin>
<IfModule mod_authz_core.c>
# Apache 2.4
Require all granted
#Require host example.com
</IfModule>
<IfModule !mod_authz_core.c>
# Apache 2.2
Order deny,allow
Allow from all
# Allow from .example.com
</IfModule>
</Location>
OR
Alias /phpPgAdmin /usr/share/phpPgAdmin
<Directory /usr/share/phpPgAdmin>
order deny,allow
deny from all
allow from 192.168.1.0/24
</Directory>
Step 4: Restart Apache Service
Restart apache service to reload new settings.
# service httpd restart
# chkconfig httpd on
On CentOS 7 systems:
systemctl enable httpd
systemctl start httpd
Configure phpPgAdmin
Edit file /etc/phpPgAdmin/config.inc.php, and do the following changes. Most of these options are self-explanatory. Read them carefully to know why do you change these values.
vi /etc/phpPgAdmin/config.inc.php
Find the following line:
$conf['servers'][0]['host'] = '';
Change it as shown below:
$conf['servers'][0]['host'] = 'localhost';
And find the line:
$conf['extra_login_security'] = true;
Change the value to false:
$conf['extra_login_security'] = false;
Find the line:
$conf['owned_only'] = false;
Set the value as true.ru
$conf['owned_only'] = true;
Save and close the file. Restart postgresql service and Apache services.
On CentOS 6.x systems:
service postgresql-9.4 restart
service httpd restart
On CentOS 7 systems:
systemctl restart postgresql-9.4
systemctl restart httpd
Step 5: Access phpPgAdmin on Browser.
Now you can access phpPgAdmin in browser using following url. Change 192.168.1.100 with your server ip.
http://192.168.1.100/phpPgAdmin/
Now open your browser and navigate to http://ip-address/phpPgAdmin. You will see the following screen.
===========================================================================================
Refer attached screen shots:
phpPgAdmin1....5
You may get an error called: Login failed.
Selection_003
This is because SELinux might restrict the users to log in to the PostgreSQL. Just enter the following command to get rid of this error.
setsebool -P httpd_can_network_connect_db 1
Now, you’ll be able to log in to the Dashboard without any problems.
This is how my phpPgAdmin dashboard looked.
That’s it. Now you’ll able to create, delete and alter databases graphically using phpPgAdmin easily.
Cheers!
=================================================================================================================
Refer: http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
PostgreSQL add or create a user account and grant permission for database:
A. You need to use following commands.
=> adduser - UNIX/Linux adduser command to add a user to /etc/passwd file
=> psql => It is a terminal-based front-end to PostgreSQL.
=> CREATE USER - Adds a new user to a PostgreSQL database cluster.
=> CREATE DATABASE - create a new database
=> GRANT ALL PRIVILEGES - define access privileges
Procedure to add a user to PostgreSQL database
To create a normal user and an associated database you need to type the following commands. The easiest way to use is to create a Linux / UNUX IDENT authentication i.e. add user tom to UNIX or Linux system first.
Step # 1: Add a Linux/UNIX user called tom
Type the following commands to create a UNIX/Linux user called tom:
# adduser tom
# passwd tom
Step # 2: Becoming a superuser
You need to login as database super user under postgresql server. Again the simplest way to connect as the postgres user is to change to the postgres unix user on the database server using su command as follows:
# su - postgres
Step #3: Now connect to database server
Type the following command
$ psql template1
OR
$ psql -d template1 -U postgres
Output:
Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
Type: \\copyright for distribution terms
\\h for help with SQL commands
\\? for help on internal slash commands
\\g or terminate with semicolon to execute query
\\q to quit
template1=#
Step #4: Add a user called tom
Type the following command to create a user called tom with a password called myPassword (you need to type command highlighted with red color):
template1=# CREATE USER tom WITH PASSWORD 'myPassword';
Step #5: Add a database called jerry
Type the following command (you need to type command highlighted with red color):
template1=# CREATE DATABASE jerry;
Now grant all privileges on database
template1=# GRANT ALL PRIVILEGES ON DATABASE jerry to tom;
Type \q to quit:
template1=# \q
Step #6: Test tom user login
In order to login as tom you need to type following commands. Login as tom or use su command:
$ su - tom
$ psql -d jerry -U tom
Output:
Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
Type: \\copyright for distribution terms
\\h for help with SQL commands
\\? for help on internal slash commands
\\g or terminate with semicolon to execute query
\\q to quit
jerry=>
==================================================================================================
Create a Postgres Table
We can create tables in postgres by using the following syntax:
CREATE TABLE new_table_name (
table_column_title TYPE_OF_DATA column_constraints,
next_column_title TYPE_OF_DATA column_constraints,
table_constraint
table_constraint
) INHERITS existing_table_to_inherit_from;
We will make a table called "popsicles" to store our popsicle varieties and some information about them.
CREATE TABLE popsicles (
pop_id serial PRIMARY KEY,
flavor varchar (50) NOT NULL,
amount int NOT NULL,
size varchar (10) check (size in ('small', 'normal', 'large'))
);
Now we can use the \dt command to see the table:
\dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | popsicles | table | postgres
(1 row)
To see the columns and data structure we just defined, we can type this command:
\d popsicles
Table "public.popsicles"
Column | Type | Modifiers
--------+-----------------------+------------------------------------
------------------------
pop_id | integer | not null default nextval('popsicles
_pop_id_seq'::regclass)
flavor | character varying(50) | not null
amount | integer | not null
size | character varying(10) |
. . .
Insert Data Into a Postgres Table
We now have a table in our database, but there's no data in it yet. We can populate it by using the insert command.
We type INSERT INTO followed by the table name. Then, we type a comma-separated list of column names inside a set of parentheses. We then type VALUES, followed by a second set of parentheses containing a comma-separated list of values that correspond to the column names.
Let's try it now. We will insert some grape popsicles into our table:
INSERT INTO popsicles (flavor, amount, size) VALUES ('grape', 10, 'normal');
INSERT 0 1
The INSERT 0 1 indicates that our command was accepted successfully. Some things to notice about this command is that the words in our value list are inside of single quotes ('), and that the command ends with a semicolon (;). Both of these points are important to remember.
Another thing to notice is that we didn't set the pop_id column. That is because we defined it as a primary key. This column must have unique values for each entry, so postgres will automatically assign the next available value to each record we create.
Let's fill in some more data so that we have a more useful table:
INSERT INTO popsicles (flavor, amount, size) VALUES ('orange', 8, 'small');
INSERT INTO popsicles (flavor, amount, size) VALUES ('fudge', 20, 'large');
INSERT INTO popsicles (flavor, amount, size) VALUES ('eclair', 14, 'normal');
INSERT INTO popsicles (flavor, amount, size) VALUES ('rainbow', 4, 'small');
If you recall, when we created the table, we defined the acceptable values for the "size" column. Postgres checks that the value is either "small", "normal", or "large". What happens if we try to provide a different value?:
INSERT INTO popsicles (flavor, amount, size) VALUES ('lime', 6, 'huge');
ERROR: new row for relation "popsicles" violates check constraint "popsicles_size_check"
DETAIL: Failing row contains (6, lime, 6, huge).
As you can see, postgres has validated that we have entered the correct data. It has rejected our newest popsicle because it didn't have one of the values that we defined for size.
Select Data From a Postgres Table
We can query our new table for data by using the select command. The select command returns data from a table that matches the criteria specified.
To return all of the info from our table, we can use the asterisk (*), which is a special symbol that means "match everything":
SELECT * FROM popsicles;
pop_id flavor amount size
1 grape 10 normal
2 orange 8 small
3 fudge 20 large
4 eclair 14 normal
5 rainbow 4 small
(5 rows)
We can select by column by replacing asterisk with the columns we want:
SELECT flavor, amount FROM popsicles;
flavor amount
grape 10
orange 8
fudge 20
eclair 14
rainbow 4
(5 rows)
If we want to filter to only show certain results, we can add a where filter on the end:
SELECT * FROM popsicles WHERE amount <= 10;
pop_id flavor amount size
1 grape 10 normal
2 orange 8 small
5 rainbow 4 small
(3 rows)
Conclusion
You should now have postgres installed on your system and have a good handle on its basic usage.
No comments:
Post a Comment