PostgreSQL Cheat Sheet : Basics


In this blog I have listed some of the basic commands like installing PostgreSQL, creating user and databases for PostgreSQL etc. CRUD with PostgreSQL is not the focus of this post. All the commands are tested in Ubuntu 14.04 LTS and Ubuntu 16.04 LTS. There may be some other richer ways to perform the operations mentioned in this post but my focus was to keep the stuff simple with minimal complexity. I have used the terms ‘user’ and ‘role’ interchangeably in the blog, as they are analogous with respect to PostgreSQL.

1) Install PostgreSQL

sudo apt-get update
sudo apt-get install postgresql -y

2) Check PostgreSQL version.

psql --version

3) Login to PostgreSQL

Login as default user ie ‘postgres’ with password as ‘password_of_your_ubuntu_system’

sudo -u postgres psql

4) Create a new user for PostgreSQL and assign a password to it

Since it is not considered a good practice to persist your data in PostgreSQL through the default user ie ‘postgres’, we will create a new user and assign a password to it. It can be done by 2 possible ways as follows ->

First Way :

sudo -u postgres createuser --interactive

Here, you would be asked name of the role/user, if you want to assign superuser privileges to the new user or not; and if not whether the user be allowed to create databases and new roles.
After creating the user, login to PostgreSQL console via the default user ie ‘postgres’ and then alter the password for the user via following command (Assuming the new user/role you created is lihas) :

ALTER USER lihas WITH PASSWORD 'lihas';

NOTE ->
In case the name of your user contains capital letters, wrap the user name into double qoutes while performing all user related operations like :

ALTER USER "LiHaS" WITH PASSWORD 'lihas';

Second Way :

Login to PostgreSQL console via the default user ie ‘postgres’ and then create the user :

CREATE USER lihas WITH PASSWORD 'lihas';     --Assuming the new user/role we want to create is lihas
ALTER USER lihas WITH CREATEDB;              --user lihas con create databases
ALTER USER lihas WITH CREATEUSER;            --user lihas con create new users/roles

5) List all users in PostgreSQL

\du

6) Switch to a user

SET ROLE user_name;

7) Check current user

SELECT CURRENT_USER;

8) Delete a user/role

DROP USER user_name;

9) List all databases

\l

10) Creating Database

CREATE DATABASE lihas_db;

By default owner of the any database you create is ‘postgres’, if you want your database to belong to a specific user, switch to that user (see command 6 above) and then create the database.

11) Enter inside a database

Enter inside database via the default user

\c database_name

OR

\connect database_name

Enter inside database with a specific user

\c database_name user_name

If the above command does not work (Peer authentication failed), simply change the database first and then switch to the user (see command 6 above).

NOTE ->
You may also login directly into a database with a certain user from the terminal by following command (with password as ‘password_of_your_ubuntu_system’ )

sudo -u role_name psql db_name

But for this command to work, role_name must be a valid Linux user name. You may add a user to Linux by following :

sudo adduser role_name;

12) Dropping database

DROP DATABASE db_name;

13) List all tables

\d

14) Describe schema of a table

\d table_name

15) Exit out of PostgreSQL

\q

Hope this compilation helps you. Share your thoughts in the comments below.


KNOLDUS-advt-sticker

Advertisements
This entry was posted in Scala. Bookmark the permalink.

6 Responses to PostgreSQL Cheat Sheet : Basics

  1. Ashish says:

    Thank you so much Sahil 🙂

  2. Douglas says:

    ‘psql –version’ only tells you the version of the client tools you happen to have installed, not the version of the PostgreSQL server. For that, you need to connect and ‘show server_version’. All of your methods of setting a password will log the password to the logs whereas using ‘\password’ will not. You do not need to pollute your operating system with additional users and use ‘sudo -u role_name psql db_name’ to login as a specific user; you should use ‘psql -U role_name db_name’.

    • Thanks for mentioning 3 major points here

      You mentioned the correct way to check PostgreSQL server as well as client version but with ‘sudo apt-get install postgresql -y’ the version of client and server are same ie. ‘9.3.17’ hence i kept it under the heading ‘ Check PostgreSQL version’

      Regarding the password updation ‘\password role_name’ is the best way, dosent keeps any history. Thanks

      Regarding the login part i completely agree ‘psql -U role_name’ is a much better way but it requires updation of pg_hba.conf file (/etc/postgresql/9.1/main/pg_hba.conf) ie changing
      # Database administrative login by Unix domain socket
      local all postgres peer

      TO
      # Database administrative login by Unix domain socket
      local all postgres md5

      Followed by restarting postgres via ‘sudo service postgresql restart’
      Since this was just a basic intro i preferred not to touch the postgres config file upgradation part

      Thanks a lot, your comment was very appropriate and informative.

  3. Douglas says:

    ‘psql –version’ only tells you the version of the client tools you happen to have installed, not the version of the PostgreSQL server. For that, you need to connect and ‘show server_version’. All of your methods of setting a password will log the password to the logs whereas using ‘\password’ will not. You do not need to pollute your operating system with additional users and use ‘sudo -u role_name psql db_name’ to login as a specific user; you should use ‘psql -U role_name db_name’.

  4. Thanks for mentioning 3 major points here
    You mentioned the correct way to check PostgreSQL server as well as client version but with ‘sudo apt-get install postgresql -y’ the version of client and server are same ie. Thanks
    Regarding the login part i completely agree ‘psql -U role_name’ is a much better way but it requires updation of pg_hba.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s