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

Discover the Best Free AI Art Tools for Your Next Masterpiece

Explore a curated collection of the finest free AI art tools, designed to help you bring your artistic vision to life.

SaaS vs. Software Agency: Choosing the Right Business Model for Your Startup

Discover the key differences between SaaS and Software Agencies to make informed business decisions and stay ahead in the competitive market.

Top ChatGPT Prompts for Technical People and Startup Founders

Discover the ultimate list of ChatGPT prompts designed specifically for technical people and startup founders. Enhance your AI interactions and boost productivity with these powerful prompts. Revolutionize the way you work and stay ahead of the competition. Read now!

Chatbots for your website

Chatbots for your website

Discover the Power of Whatsapp-web.js for Safe and Easy Automation

Automate WhatsApp with ease using Whatsapp-web.js, a NodeJS client library that connects through the official WhatsApp Web app, reducing ban risks. Perfect for user or business accounts.

Revving Up Success: How to Validate Your Car Alert App Idea

Discover the keys to success in the car alert app market. Learn how to validate your business idea, ensuring your app sends timely alerts for document expirations and service checks.

Navigating the Food Delivery Fleet Management Landscape with ManagerLivrari

Discover Manager Livrari: Your all-in-one solution for streamlining food delivery fleet management. Automate calculations, generate weekly reports, and optimize your operations with ease.

Tools for Analyzing Your Competition

Before knowing where your business stands, you should look at the market and your competition. You need to know what marketing tactics and strategies are working for your audience, what traffic the competition has, and how they position.

Free Online Photo Editor with Cool Effects | Edit Images Easily

Transform your images with our free online photo editor with cool effects. Add filters, adjust colors, and apply creative enhancements. Edit your photos easily and make them stand out.

How to find the size of a table in SQL?

Learn how to determine the size of a SQL table with this informative guide. Discover efficient methods for measuring table size.