Linux and Unix tutorials for new and seasoned sysadmin
Linux and Unix tutorials for new and seasoned sysadmin
How do I install PostgresSQL interface for perl for programming purpose under Redhat / RHEL / Fedora / CentOS Linux?
Q. I’ve installed Postgresql under Red Hat Enterprise Linux 5.x server. I’ve created username / password and database. But when I try to connect it via PHP or psql using following syntax:
psql -d myDb -U username -W
It gives me an error that read as follows:
psql: FATAL: Ident authentication failed for user “username”
How do I fix this error?
Q. I’m using Red Hat Enterprise Linux based Apache web server. How do I backup my Apache webserver, MySQL and PostgreSQL database to another disk called /backup and then copy it to other offsite backup ssh server called backup.example.com?
Q. I’ve Debian Linux VPS server for my small business web site. Can you tell me more about a free blog software (weblog software) that can run under LAMP?
Q. How do I install and configure Postgresql database server under Red hat Enterprise Linux 5 / CentOS 5 / Fedora Linux?
A. The postgresql-server package includes the programs needed to create and run a PostgreSQL server, which will in turn allow you to create
and maintain PostgreSQL databases. PostgreSQL is an advanced Object-Relational database management system (DBMS) that supports almost all SQL constructs (including transactions, subselects and user-defined types and functions). You should install postgresql-server if you want to create and maintain your own PostgreSQL databases and/or your own PostgreSQL server. You also need to install the postgresql package.
You also need to install client package called postgrsql. This package contains the docs in HTML for the whole package, as well as command-line utilities for managing PostgreSQL databases on a PostgreSQL server.
Login as the root user and enter the command:
# yum install postgresql postgresql-server
Output:
Loading "installonlyn" plugin Setting up Install Process Setting up repositories Reading repository metadata in from local files Parsing package install arguments Resolving Dependencies --> Populating transaction set with selected packages. Please wait. ---> Downloading header for postgresql-server to pack into transaction set. postgresql-server-8.1.9-1 100% |=========================| 87 kB 00:00 ---> Package postgresql-server.i386 0:8.1.9-1.el5 set to be updated --> Running transaction check --> Processing Dependency: postgresql = 8.1.9-1.el5 for package: postgresql-server --> Restarting Dependency Resolution with new changes. --> Populating transaction set with selected packages. Please wait. ---> Downloading header for postgresql to pack into transaction set. postgresql-8.1.9-1.el5.i3 100% |=========================| 119 kB 00:00 ---> Package postgresql.i386 0:8.1.9-1.el5 set to be updated --> Running transaction check Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: postgresql-server i386 8.1.9-1.el5 updates 4.0 M Installing for dependencies: postgresql i386 8.1.9-1.el5 updates 2.8 M Transaction Summary ============================================================================= Install 2 Package(s) Update 0 Package(s) Remove 0 Package(s) Total download size: 6.8 M Is this ok [y/N]: y Downloading Packages: (1/2): postgresql-server- 100% |=========================| 4.0 MB 00:05 (2/2): postgresql-8.1.9-1 100% |=========================| 2.8 MB 00:04 Running Transaction Test Finished Transaction Test Transaction Test Succeeded Running Transaction Installing: postgresql ######################### [1/2] Installing: postgresql-server ######################### [2/2] Installed: postgresql-server.i386 0:8.1.9-1.el5 Dependency Installed: postgresql.i386 0:8.1.9-1.el5 Complete!
Type the following two command:
# chkconfig postgresql on
# service postgresql start
Output:
Initializing database: [ OK ] Starting postgresql service: [ OK ]
Connect to server using
# su - postgres
Connect using psql command line tool:
$ psql -d template1 -U postgres
Output:
Welcome to psql 8.1.9, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
Finally make sure iptables allows remote access to Postgresql database server:
Open /etc/sysconfig/iptables file:
# vi /etc/sysconfig/iptables
Append following line before COMMIT line to open port 5432:
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
Save and close the file. Restart the firewall:
# service iptables restart
See also: PostgreSQL add or create a user account and grant permission for database
Q. How do I install PostgreSQL server under Ubuntu Linux?
A. PostgreSQL is an Open Source Software object relational database management system. POSTGRES pioneered many of the object-relational concepts now becoming available in some commercial databases. Traditional relational database management systems (RDBMS) support a data model consisting of a collection of named relations, containing attributes of a specific type. In current commercial systems, possible types include floating point numbers, integers, character strings, money, and dates.
The best way to install PostgreSQL under Debian or Ubuntu Linux is to use apt-get command
# apt-get install postgresql
OR
$ sudo apt-get install postgresql
Output:
Reading package lists... Done
Building dependency tree... Done
The following extra packages will be installed:
openssl postgresql-7.4 postgresql-client postgresql-client-7.4 postgresql-client-common postgresql-common ssl-cert
Suggested packages:
ca-certificates postgresql-doc-7.4
Recommended packages:
postgresql-plpython-7.4 postgresql-plperl-7.4 postgresql-pltcl-7.4
The following NEW packages will be installed
openssl postgresql postgresql-7.4 postgresql-client postgresql-client-7.4 postgresql-client-common postgresql-common
ssl-cert
0 upgraded, 8 newly installed, 0 to remove and 0 not upgraded.
Need to get 5620kB of archives.
After unpacking 14.3MB of additional disk space will be used.
Do you want to continue [Y/n]? y
Get: 1 http://cudlug.cudenver.edu stable/main openssl 0.9.8c-4 [1004kB]
......
........
..
...
Setting up postgresql-7.4 (7.4.16-1) ...
Creating new cluster (configuration: /etc/postgresql/7.4/main, data: /var/lib/postgresql/7.4/main)...
Moving configuration file /var/lib/postgresql/7.4/main/postgresql.conf to /etc/postgresql/7.4/main...
Moving configuration file /var/lib/postgresql/7.4/main/pg_hba.conf to /etc/postgresql/7.4/main...
Moving configuration file /var/lib/postgresql/7.4/main/pg_ident.conf to /etc/postgresql/7.4/main...
Configuring postgresql.conf to use port 5432...
Starting PostgreSQL 7.4 database server: main.
Setting up postgresql-client (7.5.22) ...
Setting up postgresql (7.5.22) ...
You can login as PostgreSQL superuser, enter command:
$ su - postgres
Now connect to default database called template1:
$ psql template1
Output:
[email protected]:~$ psql template1 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=#
Type \l to list database
Type \q to quit
Q. When I try to connect remote PostgreSQL, I am reciving an error which read as follows:
psql: could not connect to server: Connection refused
Is the server running on host host.domain.com and accepting
TCP/IP connections on port 5432?
How do I fix this problem? I am using CentOS 4.x version.
A. First make sure PostgreSQL server has been started to remote server.
# /etc/init.d/postgresql start
If it is running and you get above error, you need to add enable TCP/IP support. By default, the PostgreSQL server only allows connections to the database from the local machine or localhost. This is a security feature.
You need to open file called /var/lib/pgsql/data/pg_hba.conf. Login as postgres user using su command:
$ su - postgres
$ vi /var/lib/pgsql/data/pg_hba.conf
Now append following line. Let us say you would like to give access to 192.168.1.0/24 network:
host all all 192.168.1.0 255.255.255.0 trust
Please replace 192.168.1.0 and 255.255.255.0 to reflect the actual network IP address range of the clients system in your own network.
Save close the file.
You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf
$ vi /var/lib/pgsql/data/postgresql.conf
Now bind and open TCP/IP port by setting tcpip_socket to true:
tcpip_socket = true
Save and close the file.
Restart the PostgreSQL server with the following command
# /etc/init.d/postgresql restart
This will open default port 5432.
Use psql command from client system as follows:
psql -h PostgreSQL-IP-ADDRESS -U USERNAME -d DATABASENAME
Connect to remote server by IP address 192.168.1.5 and login using vivek user to connect to sales database, use:
$ psql -h 192.168.1.5 -U vivek -d sales
Where,