![\"Install](\"https:\/\/linuxize.com\/post\/how-to-install-postgresql-on-debian-9\/featured_hu3b22226b9b0d7d318005ec26e340ddcb_33785_768x0_resize_q75_lanczos.jpg?ezimgfmt=ng%3Awebp%2Fngcb87%2Frs%3Adevice%2Frscb87-1\")
<\/figure>\n\n\n\nPostgreSQL, often known simply as Postgres, is an open-source general-purpose object-relational database management system. PostgreSQL has many advanced features such as online backups, point in time recovery, nested transactions, SQL and JSON querying, multi-version concurrency control (MVCC), asynchronous replication and more.<\/p>\n\n\n\n
In this tutorial, we will show you how to install PostgreSQL on Debian 9 and explore the fundamentals of basic database administration.<\/p>\n\n\n\n
Prerequisites<\/h2>\n\n\n\n
Before proceeding with this tutorial, make sure the user you are logged in as has sudo privileges<\/a> .<\/p>\n\n\n\n At the time of writing this article, the latest version of PostgreSQL available from the Debian repositories is PostgreSQL version 9.6.<\/p>\n\n\n\n To install PostgreSQL on your Debian server complete the following steps:<\/p>\n\n\n\n Psql is an interactive terminal program that allows you to interact with the PostgreSQL server.<\/p>\n\n\n\n PostgreSQL handles database access permissions using the concept of roles. A role can represent a database user or a group of database users.<\/p>\n\n\n\n PostgreSQL supports a number of authentication methods<\/a> . The most commonly used methods are:<\/p>\n\n\n\n PostgreSQL client authentication is defined in the configuration file named The To log in to the PostgreSQL server as the postgres user first you need to switch to the user<\/a> postgres and then you can access a PostgreSQL prompt using the From here, you can interact with your PostgreSQL instance. To exit out of the PostgreSQL shell type:<\/p>\n\n\n\n You can use the The You can create new roles from the command line using the In the following example, we will create a new role named By default the PostgreSQL, server listens only on the local interface \/etc\/postgresql\/9.6\/main\/postgresql.conf<\/p>\n\n\n\n save the file and restart the PostgreSQL service with:<\/p>\n\n\n\n Verify the changes with the Installing PostgreSQL<\/h2>\n\n\n\n
sudo apt update<\/code>Copy<\/li>sudo apt install postgresql postgresql-contrib<\/code>Copy<\/li>psql<\/code> utility and print the server version<\/a> :sudo -u postgres psql -c \"SELECT version();\"<\/code>CopyThe output will look like this: version ----------------------------------------------------------------------------------------------------------- PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit (1 row)<\/code>Copy<\/li><\/ol>\n\n\n\nPostgreSQL Roles and Authentication Methods<\/h2>\n\n\n\n
pg_hba.conf<\/code> are met.<\/li>scram-sha-256<\/code> md5<\/code> and password<\/code> (clear-text)<\/li>pg_hba.conf<\/code>. By default for local connections, PostgreSQL is set to use the peer authentication method.<\/p>\n\n\n\npostgres<\/code> user is created automatically when you install PostgreSQL. This user is the superuser for the PostgreSQL instance and it is equivalent to the MySQL root user.<\/p>\n\n\n\npsql<\/code> utility:<\/p>\n\n\n\nsudo su - postgres<\/code>psql<\/code>CopyCopy<\/pre>\n\n\n\n\\q\n<\/code><\/pre>\n\n\n\nsudo<\/code> command to access the PostgreSQL prompt without switching users:<\/p>\n\n\n\nsudo -u postgres psql<\/code><\/pre>\n\n\n\npostgres<\/code> user is typically used only from the local host and it is recommended not to set the password for this user.<\/p>\n\n\n\nCreating PostgreSQL Role and Database<\/h2>\n\n\n\n
createuser<\/code> command. Only superusers and roles with CREATEROLE<\/code> privilege can create new roles.<\/p>\n\n\n\njohn<\/code> a database named johndb<\/code> and grant privileges on the database.<\/p>\n\n\n\nsudo su - postgres -c \"createuser john\"<\/code>Copy<\/li>createdb<\/code> command:sudo su - postgres -c \"createdb johndb\"<\/code>Copy<\/li>john<\/code> user on the database we created in the previous step, connect to the PostgreSQL shell:sudo -u postgres psql<\/code>Copyand run the following query:GRANT<\/strong> ALL<\/strong> PRIVILEGES<\/strong> ON<\/strong> DATABASE<\/strong> johndb TO<\/strong> john;<\/code>Copy<\/li><\/ol>\n\n\n\nEnable remote access to PostgreSQL server<\/h2>\n\n\n\n
127.0.0.1<\/code>. To enable remote access to your PostgreSQL server open the configuration file postgresql.conf<\/code> and add listen_addresses = '*'<\/code> in the CONNECTIONS AND AUTHENTICATION<\/code> section.<\/p>\n\n\n\nsudo vim \/etc\/postgresql\/9.6\/main\/postgresql.conf<\/code><\/pre>\n\n\n\n#------------------------------------------------------------------------------\n# CONNECTIONS AND AUTHENTICATION\n#------------------------------------------------------------------------------\n\n# - Connection Settings -\n\nlisten_addresses = '*' # what IP address(es) to listen on;\n<\/code><\/pre>\n\n\n\nsudo service postgresql restart<\/code><\/pre>\n\n\n\nss<\/code> utility:<\/p>\n\n\n\nss -nlt | grep 5432<\/code><\/pre>\n\n\n\nLISTEN 0 128 0.0.0.0:5432 0.0.0.0:*\nLISTEN 0 128 [::]:5432 [::]:*\n<\/code><\/pre>\n\n\n\n