Installing PostgreSQL is easy but user creation and setting
for user password can create issue. You can install the PostgreSQL on Linux
server by using some command that I have mention here. I have used Centos Server
so I have shared centos server commands.
I have shared step by step installation and configuration.
Step 1 — Installing PostgreSQL
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum list postgresql*
Step 2 — Creating a New PostgreSQL Database Cluster
sudo yum install postgresql11-server
I have used PostgreSQL-11 version or you can use here PostgreSQL version that you want to install .
sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
sudo systemctl start postgresql-11
sudo systemctl enable postgresql-11
Step 3 — Using PostgreSQL Roles and Databases
Switch over to the postgres account on your server by typing:
sudo -i -u postgres
sudo -u postgres psql
Step 4 — Creating a New Role
If you are logged in as the postgres account, you can create a new user by typing:
sudo -u postgres createuser –interactive
The script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user to your specifications. For this tutorial, create a sammy user and give it superuser privileges:
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y
Step 5 — Creating a New Database
sudo -u postgres createdb sammy
Step 6 — Opening a Postgres Prompt with the New Role
If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the postgres user):
sudo adduser sammy
Once this new account is available, you can either switch over and connect to the database by typing:
sudo -i -u sammy
Or, you can do this inline:
sudo -u sammy psql
This command will log you in automatically.
If you want your user to connect to a different database, you can do so by specifying the database like this:
psql -d postgres
Once logged in, you can check your current connection information by typing:
Configure PostgreSQL to allow remote connection
In order to fix this issue we need to find postgresql.conf. In different systems it is located at different place. I usually search for it.
$ find / -name “postgresql.conf”
/var/lib/pgsql/9.4/data/postgresql.conf location may be change in you server
Open postgresql.conf file and replace line
listen_addresses = ‘localhost’
listen_addresses = ‘*’
Now restart postgresql server.
Let’s try to connect to a remote PostgreSQL server using “psql”.
In order to fix it, open pg_hba.conf and add the following entry at the very end.
host all all 0.0.0.0/0 md5
host all all ::/0 md5
To change the Postgres user’s password follow these steps
Login into the psql:
$ sudo -u postgres psql
Then in the psql console change the password and quit:
postgres=# \password postgres
Enter new password: <new-password>
or using a query
ALTER USER postgres PASSWORD ‘<new-password>’;
or in one line
sudo -u postgres psql -c “ALTER USER postgres PASSWORD ‘<new-password>’;”
sudo service postgresql restart