This is a simple guide for setting up a standalone PostgreSQL database server on Debian.

• Use sudo to get a root shell
  

  sudo -s
  

• Update the system:
  

  apt-get update && sudo apt-get upgrade
  

Install PostgreSQL¶

• Install PostgreSQL
  

  apt-get install postgresql postgresql-client
  

Configure PostgreSQL¶

• Edit pg_hba.conf to enable database access via the local socket:
  

  nano /etc/postgresql/9.4/main/pg_hba.conf
  

  • Replace peer with trust
    

    local   all         all                               trust   
    

• Edit the main postgresql config:
  

  nano /etc/postgresql/9.4/main/postgresql.conf
  

  • And change the listen_addresses parameter:
    

    listen_addresses = '*'
    

• Then append a rule to enable network access from a given subnet:
  

  echo "host    all     all     192.168.0.0/24  trust" >> /etc/postgresql/9.4/main/pg_hba.conf
  

• And restart postgresql:
  

  service postgresql restart
  

Create a database and user¶

• Connect to the default database:
  

  su - postgres
  psql
  

  • Set a password for the postgres user:
    

    \password postgres
    

  • Create a new user, the postgres admin console will show postgres=#:
    

    CREATE USER somepguser WITH PASSWORD 'somepguserpass';
    

  • And a create a database:
    

    CREATE DATABASE somepgdatabase OWNER somepguser;
    

  • Quit from the database
    

    \q
    

• Exit from the postgres user
  

  exit
  

• Connect as somepguser to the new database:
  

  psql -d somepgdatabase -U somepguser
  

  • If connecting from a remote host, connect using the following:
    

    psql -h pg.example.com -d somepgdatabase -U somepguser
    

Resources¶

• https://wiki.debian.org/PostgreSql