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

TikTok Ads Strategy from a SaaS founder

Here's a short tutorial from my disrupting strategy of doing Tiktok Ads as a SaaS Founder

Curated SEO Resources: Essential Tools and Tips

Discover a handpicked selection of indispensable SEO resources, including tools and tips, to optimize your website's performance.

A Week in the Life of an Invoice Wrangler: Navigating Ridesharing and Food Delivery Chaos

As an app founder in the ridesharing and food delivery industry, I found myself knee-deep in invoice reports from companies like Bolt, Uber, Glovo, and Bolt Food

Free HTML templates list for Startups

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

Deal with client requests in SaaS

How to deal with client requests in Saas which are seeing only their interests, not the product interest.

The first client of LoyalXpert is not answering anymore

Trying to implement LoyalXpert app, I lost my first customer, he's not answering anymore

Experiments with Tiktok Ads

Recently tried out TikTok ads for the first time and here are some of my learnings and challenges

People don’t care about you, until they know you care about them.

People don’t care about you, until they know you care about them. The same happens in business, you need to take care of your clients.

The One Word That Can Ruin Your SaaS Business Anyone

As a SaaS founder, you probably know how important it is to have a clear and specific target market for your product.

How I got my digital certificate connected it with ANAF

How I got my digital certificate from certSIGN and connected it with ANAF