Below is a short instruction on how to install PostgreSQL.
1. Let's install PostgreSQL:
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
2. Next, let's install graphical client pgAdmin for PostgreSQL:
sudo apt-get install pgAdmin3
After all installations you have done, add minimal changes to config files.
The working directory by default is /etc/postgresql/{version}/main/
(the graphical installer installs in /opt/, then the working directory is
/opt/PostgreSQL-{version}/data). It contains the main configuration files.
1. Allow TCP/IP connections
In the configuration file postgresql.conf (the default is in
/etc/postgresql/{version}/main/), uncomment the line
#listen_addresses = 'localhost' by deleting the # character.
listen_addresses = 'localhost'
To connect to the server from other machines, the value 'localhost' should be replaced with the IP address of the machine, or 0.0.0.0, or simply put '*'.
All the necessary details can be found in the comments in the configuration file.
2. User settings
When you install the server, it creates a user postgres with a password postgres. These rights allow the server communicate with the operating system (as I know). But for work it's not enough. You must set a password for the same user in database. To do this, run the psql console client with the postgres user right:
sudo -u postgres psql
Then execute the query:
alter user postgres with encrypted password '_password_'
Where '_password_' is the password for connecting to the server under the user postgres.
3. Changing the authentication method:
In the configuration file pg_hba.conf (the default is in
/etc/postgresql/{version}/main/), change the authentication method for the postgres user to md5:
local all postgres md5
Restart the server:
sudo /etc/init.d/postgresql restart
pgAdmin configuration
1. Run pgAdmin
2. Create a new connection to the server: File-> Add server
3. Give the connection a name, specify it as the localhost host, as the postgres user with the password created when configuring the server
4. If everything is well configured, there should not be any problems.
Note: If you need a remote connection to the server, you need to make the following settings:
1. Add or edit the following line in your postgresql.conf:
listen_addresses = '*'
2. Add the following line as the first line of pg_hba.conf.
It allows access to all databases for all users with an encrypted password:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 0.0.0.0/0 md5
3. Restart the server:
sudo /etc/init.d/postgresql restart
psql console commands
Connect to database \c database_name
List of databases \l
List of tables \dt
List of columns \d table_name
Table description \d+ table_name
List of users \du
Exit from psql \q
Server management commands
sudo service postgresql (start|stop|reload|restart|status)