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
\ducommand 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
\lcommand 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
\conninfocommand 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
\dtcommand 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
\qcommand to logout the PostgreSQL server command console and login with$ sudo -u postgres psqlcommand again and then change the current database with the command\c dev2qaand then create the table again. - The
\dtcommand 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
\qcommand 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, clickqto 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.
