Setting Up Postgres Server on Ubuntu

Installing Postgres

• Update and Upgrade the server
• sudo apt install wget ca-certificates to install wget and ca-certificates
• Get the certificate and add it to apt-key management
  • wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
  • sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
  • Throw and Error: -> install this missing dependency : apt install gnupg
• Update the server : sudo apt update
• Install Postgres : apt install postgresql postgresql-contrib
• Check Postgres : service postgresql status

Setting Up Postgres Server

• Enable connection from the client nano /etc/postgresql/14/main/postgresql.conf
  • Uncomment and replace ip_address with * : listen_addresses = '*'
• Edit the PostgreSQL access policy nano /etc/postgresql/14/main/pg_hba.conf
  • Append the following line : host all all 0.0.0.0/0 md5
• Restart the Server systemctl restart postgresql

Creating a new User and Database

• Creating a new user sudo -u postgres createuser <username>
• Creaing a new databse sudo -u postgres createdb <dbname>
• Change Password and grant privileges on database sudo -u postgres psql alter user <username> password '<password>'; grant all privileges on database <dbname> to <username> ;

Check the Connection using

• psql -h <ip_address> -p 5432 -d <database> -U <username>