How to setup a Postgres Server on Ubuntu


Installing on Linux

$ sudo apt-get update

$ sudo apt-get install postgresql postgresql-contrib

Switch over to the postgres account on your server by typing:

$ sudo -i -u postgres

You can now access a Postgres prompt immediately by typing: $ psql, exit with \q

Creating user

$ sudo -u postgres createuser <username>

Creating Database

$ sudo -u postgres createdb <dbname>

Giving the user a password

$ sudo -u postgres psql
psql=# alter user <username> with encrypted password '<password>';

Granting privileges on database

psql=# grant all privileges on database <dbname> to <username> ;

Access remotely

  1. $ sudo su - postgres
  2. postgres:$ vi /etc/postgresql/9.5/main/postgresql.conf Search after listen_addresses and uncomment that line or add this listen_addresses = '*'
  3. postgres:$ vi /etc/postgresql/9.5/main/pg_hba.conf Append at the end of the file these:
host    all     all     0.0.0.0/0       md5
host    all     all     ::/0            md5
  1. Restart postgres
$ sudo service postgresql restart

PS: Make sure that you have postgres version 9.5 $ psql --version . If the file it’s empty try to use autocomplete vi /etc/postgresql/ and then press tab for version autocomplete.

Useful commands

Show all databases \l+

Show all roles \du+

Show all tables \dt

Reset auto increment counter in postgres

Firstly you need to find your sequence identifier for the column that you want to reset the counter. Usually the name it’s the name of the table followed by column name followed by _seq.

Example:

table name: ‘users’ column name: ‘id’

sequence name will be: users_id_seq

First you need to find the current maximum value:

SELECT setval('users_id_seq', max(id)) FROM  accounts;

then you can reset it with one of these commands:

SELECT setval('accounts_id_seq', DESIRED_VALUE);

or

ALTER SEQUENCE accounts_id_seq RESTART WITH DESIRED_VALUE;

Newsletter


Related Posts

Get your Jenkins Passwords from secrets

How to get your Jenkins passwords from your secrets credentials. Follow this simple tutoriale and find your password or ssh private key.

How to launch a 6 figures startup with 0$ investment

Are you curious how to launch a startup in the fast and effective way? Here's not the answer, but you can understand how to achieve it

How to cold email

How to cold email - a practical guide about how to use cold email in your activity as a CEO of a software agency or startup owner.

Linux disk space alerts on Discord

Linux disk space alerts. Check your space and be responsible with your project. Take care of your space and don't crack the project.

Git clone private repo in docker

A simple how to guide to use git clone of a private repository inside docker container. With vuejs / nodejs app.

How to detect the browser language in javascript

How to detect the browser language in javascript in just a line of code. The method is working on all major browser

You don't need a macbook pro to be a pro

You don't need a macbook pro to be a pro! You need a laptop and internet connection, and a brain, but you already have it

Free templates for startup

Free HTML templates for startup or a new project. A complete list with free resources to build your next startup's website and gain the traction to the sky.

What I learned to never do in a presentation meeting

What I learned to never do in a presentation meeting. I had a very bad meeting experience with a gateway provider.

Startup graveyard is a series of killed projects that made millions in revenue per year

Startup graveyard is a series of killed projects that made millions in revenue per year. Just watch their graveyard and do it your own. What better validation do you expect.