How To Install And Manage ( Create, Read, Update, Delete ) PostgreSQL Database On Ubuntu Linux

PostgreSQL is a popular open source RDBMS database widely used in programming. It is supported by multiple programming languages such as python, java etc. This article will tell you how to install and manage it on Ubuntu Linux both with command line and pgAdmin3 ( a graphical user interface tool).

1. Install & Manage PostgreSQL With Command Line.

1.1 Install PostgreSQL.

Open a terminal in Ubuntu and run below command to install postgresql.

$ sudo apt-get install postgresql-10

After successfully install it, your postgresql database server will be started automatically. You can also start the postgresql database server use the command line printed out in the terminal console like below.

Success. You can now start the database server using: /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Success. You can now start the database server using:

    /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
update-alternatives: using /usr/share/postgresql/10/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for systemd (237-3ubuntu10.19) ...
Processing triggers for ureadahead (0.100.0-20) ...

1.2 Connect PostgreSQL Server.

Now you can run command line to connect to above postgresql database server, and list all databases, tables and create tables etc.

Connect postgresql database server with user postgres ( the default username is postgres and the default db name is postgres also ). Because we have root permission, so do not need to input user postgres’s password.

:~$ sudo -u postgres psql
psql (10.7 (Ubuntu 10.7-0ubuntu0.18.04.1))
Type "help" for help.

You can also connect local postgresql database server with below command.

# psql database_name 'postgresql_user_name'

$ psql postgres 'postgres'

If you encounter error likepsql: FATAL: Peer authentication failed for user “postgres”, you can read article How To Resolve Psql: Fatal: Peer Authentication Failed For User “postgres” Error When Login Postgresql In Command Line to fix it.

1.3 Alter User postgres’s Password.

After you login postgres db server with above command, you need to alter the user postgres’s password, then you can connect to the postgresql db server with it’s GUI tool –  pgAdmin3 later.

postgres=# alter user postgres with password '888888';
ALTER ROLE

1.4 List All PostgreSQL Users.

\du command can list all current postgresql users.

postgres-# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

1.5 Create & Drop User.

Run CREATE USER [user_name] WITH PASSWORD [‘password’]; command to create user, run DROP USER [‘user_name’]; to drop user.

postgres=# CREATE USER test_user WITH PASSWORD 'test_user';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user |                                                            | {}

postgres=# drop user 'test_user'

1.6 List All Current Database Schemas.

\l command is used to list all databases.

postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

1.7 Create A New PostgreSQL Database dev2qa.

Please note the CREATE DATABASE command should end with a semicolon ( ; ).

postgres=# CREATE DATABASE dev2qa;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 dev2qa    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

1.8 Display Current Connection Information.

\conninfo command will display current db connection info.

postgres-# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

1.9 Connect To Newly Added Database dev2qa.

\c [DB_NAME] command will connect to the provided database. Please note after the command execute successfully the psql prompt string is changed to new database name dev2qa.

postgres-# \c dev2qa
You are now connected to database "dev2qa" as user "postgres".
dev2qa-#

1.10 List Tables In Current Database.

The \dt command is used to list all current database contained tables.

dev2qa-# \dt
Did not find any relations.

1.11 Create Table In Current Database.

Run CREATE TABLE sql command to create postgresql db table. If you find the CREATE TABLE sql command do not work, you should run \q command to logout the postgresql server command console and login with $ sudo -u postgres psql command again and then change the current database with command \c dev2qaand then create the table again.

READ :   How To Run Unit Test Use External CSV Test Data In Python

The \dt command is used to list user created database tables.

dev2qa=# CREATE TABLE dept( dept_id serial PRIMARY KEY,  dept_name VARCHAR (50) UNIQUE NOT NULL, dept_desc VARCHAR (500) NOT NULL, create_time TIMESTAMP NOT NULL);
CREATE TABLE
dev2qa=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | dept | table | postgres
(1 row)

1.12 List Table Definition.

\d [TABLE_NAME] command can list table definition.

dev2qa-# \d dept
                                           Table "public.dept"
   Column    |            Type             | Collation | Nullable |                Default                
-------------+-----------------------------+-----------+----------+---------------------------------------
 dept_id     | integer                     |           | not null | nextval('dept_dept_id_seq'::regclass)
 dept_name   | character varying(50)       |           | not null | 
 dept_desc   | character varying(500)      |           | not null | 
 create_time | timestamp without time zone |           | not null | 
Indexes:
    "dept_pkey" PRIMARY KEY, btree (dept_id)
    "dept_dept_name_key" UNIQUE CONSTRAINT, btree (dept_name)

1.13 Insert Data Into PostgreSQL Table.

Do not forget the simicolon(;) at the sql command ending, otherwise the sql command will not execute.

dev2qa=# INSERT INTO dept(dept_id, dept_name, dept_desc, create_time) values('1', 'Dev', 'Develop Department',now())
INSERT 0 1
dev2qa=# insert into dept(dept_id, dept_name, dept_desc, create_time) values('2', 'QA', 'Quality Assurance',now());
INSERT 0 1
dev2qa=# select * from dept;
 dept_id | dept_name |     dept_desc      |        create_time        
---------+-----------+--------------------+---------------------------
       1 | Dev       | Develop Department | 2019-04-25 19:18:29.23326
       2 | QA        | Quality Assurance  | 2019-04-25 19:19:23.04152
(2 rows)

1.14 Drop Table.

dev2qa=# drop table dept;
DROP TABLE
dev2qa=# \d
Did not find any relations.

1.15 Drop Database.

Please note you can not drop currently used database.

dev2qa=# drop database dev2qa;
ERROR:  cannot drop the currently open database

So you should change the current database if you want to drop it like below.

dev2qa=# \c postgres 
You are now connected to database "postgres" as user "postgres".
postgres=# 
postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 dev2qa    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

postgres=# 
postgres=# drop database dev2qa;
DROP DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

1.16 Quit Postgresql Command Console.

Execute \q command to exit the psql console.

READ :   How To Operate Foreign Key And Many To Many Field In Django Model

1.17 Get Command Help.

If you want to know more about the postgresql command, please run \?in terminal to list all command help information, then you will enter help manual list page, click q to exit the command help list page.

2. Manage PostgreSQL Database Server With PgAdmin3.

The pgAdmin3 is a GUI tool to manage postgresql database server. It is very easy to use than command line. This section will tell you how to use it.

2.1 Install PgAdmin3.

To install it you can run below command in a terminal.

$ sudo apt-get install pgadmin3

2.2 Start PgAdmin3 & Connect PostgreSQL Database Server.

Run pgadmin3 command in terminal to start the postgresql GUI manager. After start, you can see below window popup, click the bolt button at window top left conner to open New Server Registration dialog. Input username and password of the login user, now you should know why we alter the postgres user’s password in command line, because without password, you can not login to postgres database server here.

$ pgadmin3
Gtk-Message: 19:34:03.311: Failed to load module "canberra-gtk-module"

connect to local postgresql server with pgadmin3

2.3 Create PostgreSQL Database Use pgAdmin3.

  1. Right click the databases node, click New Database… menu item in the popup menu list.
    create new postgresql database menu item
  2. Then a new database dialog will be shown, input db name and select db owner, click OK button to create it.
    new postgresql database dialog window

2.4 Create Table With pgAdmin3.

  1. Expand the database dev2qa —> Schemas —> public —> Tables, right click the Tables node then click New Table… menu item in the popup menu list.
    create new postgresql database table menu item
  2. Input table name, and define table columns in the Columns tab. Click OK button, then a table will be created.
  3. You can also use pgAdmin3 to do various postgresql database operations easily. We will not introduce them all, you can try it your self.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.