Install and Connect Command Line PostgreSQL
Install and Connect Command Line PostgreSQL seems very ordinary to accomplish but it can turn into a big mess when you are not following the proper instructions. There are few things which must be dealt with great care. I faced this issue while installing and connecting it in my local environment. Fedora operating system was installed only with text interface and there was no graphical user interface. I Used under given command to install the PostgreSQL on Fedora 22 server.
[themify_quote]dnf -y install postgresql93 postgresql93-server postgresql93-contrib postgresql93-libs –enablerepo=pgdg93[/themify_quote]
After successfully clearing the dependencies it will install the PostgreSQL server. now you need to configure the startup script for PostgreSQL to start when the server boots.
[themify_quote]sudo systemctl enable postgresql[/themify_quote]
Use under given commands to start the postgresql service:
[themify_quote]/usr/pgsql-9.3/bin/postgresql93-setup initdb[/themify_quote]
[themify_quote]systemctl start postgresql-9.3[/themify_quote]
Now you are done with the installation of the PostgreSQL on your server.
After successful installation of the PostgreSQL now you need to configure this server to be connected from your local network so that users can directly attach any application to this database server.
Create a user and database so that you can connect to it using PGAdminIII,
Use the under given command to interact with postgreSQL:
[themify_quote]su – postgres[/themify_quote]
and then run psql command to enter into postgreSQL command line. after that reset the password of postgres user. enter the under given command:
[themify_quote]\password postgres[/themify_quote]
once you are done with the password reset of this user create a database by using the under given command:
[themify_quote]createdb database-name[/themify_quote]
Once you are done with these settings, quit the postgres command line and switch to root user mode so that you can make changes in few files.
The postgresql server is using two main configuration files
- /var/lib/pgsql/data/postgresql.conf
- /var/lib/pgsql/data/pg_hba.conf
Postgresql.conf
If you want postgreSQL to accept network connections, you should change
listen_addresses = ‘localhost’
to
listen_addresses = ‘*’
pg_hba.conf
Once your database is set up, you need to configure access to your database server. This may be done by editing file /var/lib/pgsql/data/pg_hba.conf. There are rules like this in the file:
# TYPE DATABASE USER ADDRESS METHOD host all all 127.0.0.1/32 md5 local all postgres peer
Check your and security settings and choose your appropriate settings in this file. I was working with this server locally so I opened it for complete network by putting the network ID and netmask in the allowed IP address under md5.
Firewall
PostgreSQL operates on port 5432 (or whatever else you set in your postgresql.conf). In firewalld you can open it like this:
$ # make it last after reboot $ firewall-cmd –permanent –add-port=5432/tcp $ # change runtime configuration $ firewall-cmd –add-port=5432/tcp
In case of iptables:
$ iptables -A INPUT -p tcp –dport 5432 -m state –state NEW,ESTABLISHED -j ACCEPT
This will open the 5432 port for the whole world, make it available for your specified IP addresses only. follow the firewall guide on fedora website.
SELinux
If you have SELinux enforced, you may run into trouble when trying to do some non-standard configuration. For example if you would like to change a location of your database, you have to add new context mapping for the new location:
$ semanage fcontext -a -t postgresql_db_t "/my/new/location(/.*)?"
If default port doesn’t work for you, you may need to map postgre’s port type to your desired port:
$ semanage port -a -t postgresql_port_t -p tcp 5433
If you install a webapp that wants to communicate with PostgreSQL via TCP/IP, you will have to tell SELinux to allow this on the webserver host:
# setsebool -P httpd_can_network_connect_db on
I ope this will help someone to accomplish this task in a timely fashion and save couple of very important minutes of life to work on some other very important tasks.
if you like to read more about this topic, like how to optimize your server, how to increase connection on postgreSQL server, please follow this URL:
