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

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.

How do you detect Credit card type based on the number?

How do you detect Credit card type based on the number using JavaScript? Simple solution using only code, no library required.

Download files in Javascript from Node.js server

How to download files in Javascript, either you use Vue.js, React, Angular, jQuery, or Vanilla JS. On the backend side, we run on Node.js using Express.js.

What books to read

The list of books I read. I decided one year ago to challenge myself and read as many books as I can this year. Take a look at this article and find all of them.

Startup tools for a better version

Are you a startup founder and are you looking for startup tools like invoice, planning or just making money with much automation? Check this article, from founder to founders.

How to use express validator?

How to use express validator through full examples. I found express validator very powerful, but having a poor documentation.

How to check DNS propagation

How to check DNS propagation? Did you changed the DNS records recently and now is not working? Stop crying and check the DNS propagation.

LibraPay library for Nodejs

LibraPay library for Nodejs - integrate Libra Bank online payment gateway using this library for NodeJs.

Opensource Object Storage with Minio

Opensource Object Storage with Minio using Docker. An alternative to AWS S3, Linode Storage, Google Storage, Azure Storage.