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

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 PostgreSQL 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 the 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

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 the command line to connect to the above PostgreSQL database server, and list all databases, tables and create tables, etc.
Connect to the 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 to the local PostgreSQL database server with the below command.
```
# psql database_name 'postgresql_user_name'

$ psql postgres 'postgres'
```
If you encounter an error like psql: FATAL: Peer authentication failed for user “postgres”, you can read the 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 log in PostgresSQL DB server with the 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.

The \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.

The \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.

The \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 the 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 does 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 the command \c dev2qa and then create the table again.

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.

The \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 semicolon(;) 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.

Run the drop table table-name SQL command to drop the table.

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

1.15 Drop Database.

Run the drop DB-name to drop the PostgreSQL database.

Please note you can not drop the 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.

1.17 Get PostgreSQL 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 the PostgreSQL database server. It is very easy to use than the command line. This section will tell you how to use it.

2.1 Install PgAdmin3.

To install PgAdmin3 you can run the 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 the start, you can see the PostgreSQL manager window popup.
```
$ pgadmin3
Gtk-Message: 19:34:03.311: Failed to load module "canberra-gtk-module"
```

Click the bolt button at the PostgreSQL manager window top left corner to open the 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 the command line because without a password you can not register to the PostgreSQL database server in the New Server Registration window here.

2.3 Create PostgreSQL Database Use pgAdmin3.

Right-click the Databases node in the PostgreSQL Manager window left panel, click New Database… menu item in the popup menu list.

Then a New Database… dialog will be shown, input DB Name (dev2qa) and select DB Owner (postgres), click the OK button to create it.

2.4 Create Table With pgAdmin3.

Expand the database dev2qa ( that is created in the above ) —> Schemas —> public —> Tables, right-click the Tables node then click New Table… menu item in the popup menu list.
Input table name, and define table columns in the Columns tab. Click the OK button, then a PostgreSQL table will be created.

You can also use pgAdmin3 to do various PostgreSQL database operations easily. We will not introduce them all, you can try it yourself.