How To Install PostgreSQL 12 on Ubuntu 20.04/18.04/16.04

In this article, we’ll show you how to  Install PostgreSQL 12 on Ubuntu 20.04/18.04/16.04. PostgreSQL is an open-source database system and It is powerful like other relational database systems.

PostgreSQL is an open-source relational database management system that is widely used by developers for storing and managing large amounts of data. PostgreSQL provides a rich set of features, including transactions, concurrency control, and high availability, making it a popular choice for web applications, data warehousing, and business intelligence. In this tutorial, we will guide you through the process of installing PostgreSQL 12 on Ubuntu 20.04/18.04/16.04.

System Requirements:

The system requirements for installing PostgreSQL 12 on Ubuntu 20.04, 18.04, and 16.04 can vary depending on the specific needs of your application and the expected workload. However, here are general guidelines:

1. Ubuntu Version:
   • PostgreSQL 12 is officially supported on Ubuntu 20.04, 18.04, and 16.04. Ensure that you are running one of these versions.
2. Hardware Requirements:
   • Processor: A modern multi-core processor. PostgreSQL benefits from having multiple CPU cores, especially in a production environment.
   • Memory (RAM): At least 1 GB of RAM for basic installations. For larger databases and production environments, consider 8 GB or more.
   • Storage: PostgreSQL performance is influenced by disk I/O. Ensure sufficient disk space for the database, and consider using SSDs for better performance.
3. Software Dependencies:
   • Ensure that the system is up-to-date with the latest updates and patches.
   • PostgreSQL 12 requires certain libraries and tools, such as libreadline and libssl. Install these dependencies before attempting to install PostgreSQL.
4. Network Connectivity:
   • PostgreSQL uses a client-server model, so network connectivity is essential.
   • Ensure that the necessary ports (default is 5432) are open and accessible.
5. User Permissions:
   • The installation process may require administrative privileges. Ensure that the user performing the installation has the necessary permissions.
6. Firewall Configuration:
   • If a firewall is enabled, ensure that it allows traffic on the PostgreSQL port (default is 5432) and any other ports required by your application.
7. Locale Settings:
   • PostgreSQL relies on the locale settings of the operating system. Make sure that the locale is set appropriately to support the character encoding you plan to use.
8. Backup and Recovery Considerations:
   • Plan for backup and recovery by having sufficient disk space for backups and a strategy for regular backups.

Always refer to the official documentation for PostgreSQL and the specific guidelines provided by the PostgreSQL community for your operating system version. Additionally, consider the specific requirements of your application and adjust the resources accordingly for optimal performance.

Best Practices, Dos and Don’ts, and Common Mistakes for Working with PostgreSQL presented in a table format:

Here are some dos and don’ts, as well as common mistakes, when working with PostgreSQL:

Dos:

1. Do Use Schemas: Organize your database objects into schemas to better manage and secure your data.
2. Do Regular Backups: Implement a reliable backup strategy to safeguard your data against loss or corruption.
3. Do Use Transactions: Use transactions to ensure data consistency and integrity in your database operations.
4. Do Index Properly: Create indexes on columns frequently used in search and join operations to improve query performance.
5. Do Optimize Queries: Use EXPLAIN ANALYZE to analyze and optimize your queries for better performance.
6. Do Set Proper Data Types: Choose appropriate data types for your columns to save storage space and improve query efficiency.
7. Do Monitor and Tune Performance: Regularly monitor your database’s performance and make adjustments as needed, such as optimizing queries or increasing resources.
8. Do Use Connection Pooling: Implement connection pooling to efficiently manage database connections and reduce overhead.
9. Do Keep Software Up to Date: Keep your PostgreSQL server and any associated tools or libraries up to date to benefit from bug fixes and improvements.
10. Do Read the Documentation: PostgreSQL has comprehensive documentation; refer to it to understand the features and best practices.

Don’ts:

1. Don’t Use Reserved Words: Avoid using PostgreSQL’s reserved words as column or table names to prevent naming conflicts.
2. *Don’t Use SELECT : Avoid using “SELECT *” in your queries; instead, specify the specific columns you need to reduce unnecessary data transfer and improve query performance.
3. Don’t Ignore Security: Always apply appropriate security measures, including proper authentication and authorization settings.
4. Don’t Overuse Triggers: Excessive use of triggers can make your database harder to maintain and troubleshoot.
5. Don’t Use Large Object Types for Large Data: PostgreSQL’s Large Object types are less efficient than regular table storage for large data; use them sparingly.
6. Don’t Ignore Connection Management: Carefully manage your database connections and ensure they are closed properly to avoid resource leaks.

Common Mistakes:

1. Not Using Indexes: Failing to use indexes on frequently queried columns can result in slow query performance.
2. Ignoring Vacuuming: Neglecting the vacuuming process can lead to table bloat and performance degradation over time.
3. Lack of Backups: Not implementing regular backups can result in data loss in case of hardware failures or data corruption.
4. No Connection Pooling: Failing to use connection pooling can overload your database with unnecessary connection overhead.
5. Not Monitoring Performance: Not monitoring and tuning the database for performance can lead to suboptimal query execution and user experience.
6. Ignoring Security: Inadequate security settings can expose sensitive data or make your database vulnerable to attacks.
7. Ignoring Data Types: Using inefficient data types or not utilizing PostgreSQL’s advanced data types for specific purposes can lead to wasted storage and reduced performance.
8. Poorly Designed Queries: Writing inefficient SQL queries can result in excessive resource consumption and slow response times.
9. Not Planning for Scalability: Failing to plan for database scalability can lead to performance bottlenecks as your application grows.
10. Neglecting Documentation: Lack of proper documentation can make it difficult for you and your team to understand and maintain the database over time.

By following the dos, avoiding the don’ts, and being aware of common mistakes, you can work more effectively with PostgreSQL and maintain a robust and performant database system.

You can view the release page of PostgreSQL 12. 

Steps to Install PostgreSQL 12 on Ubuntu

Prerequisites

Before we begin the installation, make sure you have the following:

1. A system running Ubuntu 20.04.
2. Superuser (root) access or a user with sudo privileges.

Step 1: Update Package List

It is always recommended to update the system before installing PostgreSQL. To start the installation process, let’s ensure that the package list is up to date. Open a terminal and type the following command:

sudo apt update

Step 2: Install the required package 

sudo apt -y install vim bash-completion wget

If the package is already installed then you will get the below output, you can ignore it.

Sample Output:

root@PostgreSQL:~# sudo apt -y install vim bash-completion wget
Reading package lists... Done
Building dependency tree 
Reading state information... Done
bash-completion is already the newest version (1:2.11-2ubuntu1).
vim is already the newest version (2:8.2.0716-3ubuntu2).
wget is already the newest version (1.20.3-1ubuntu1).
wget set to manually installed.
The following package was automatically installed and is no longer required:
virtualbox-guest-utils
Use 'sudo apt autoremove' to remove it.
0 upgraded, 0 newly installed, 0 to remove and 212 not upgraded.
root@PostgreSQL:~# 

Step 3: Add PostgreSQL 12 repository

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list

Step 4: Install PostgreSQL 12 on Ubuntu

We can now proceed to install PostgreSQL 12 using the following command:

sudo apt update

sudo apt -y install postgresql-12 postgresql-client-12

Sample Output:

root@PostgreSQL:~# sudo apt -y install postgresql-12 postgresql-client-12
Reading package lists... Done
Building dependency tree 
Reading state information... Done
The following package was automatically installed and is no longer required:
virtualbox-guest-utils
Use 'sudo apt autoremove' to remove it.
The following additional packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libpq5
libtypes-serialiser-perl pgdg-keyring postgresql-client-common
postgresql-common sysstat
Suggested packages:
postgresql-doc-12 isag
The following NEW packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libpq5
libtypes-serialiser-perl pgdg-keyring postgresql-12 postgresql-client-12
postgresql-client-common postgresql-common sysstat
0 upgraded, 11 newly installed, 0 to remove and 212 not upgraded.
Need to get 17.5 MB of archives.
After this operation, 59.4 MB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt groovy-pgdg/main amd64 libpq5 amd64 13.3-1.pgdg20.10+1 [177 kB]
Get:2 http://us.archive.ubuntu.com/ubuntu groovy/main amd64 libcommon-sense-perl amd64 3.75-1build2 [20.5 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu groovy/main amd64 libjson-perl all 4.02000-2 [80.9 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt groovy-pgdg/main amd64 pgdg-keyring all 2018.2 [10.7 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt groovy-pgdg/main amd64 postgresql-client-common all 226.pgdg20.10+1 [90.6 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu groovy/main amd64 libtypes-serialiser-perl all 1.0-1 [12.1 kB]
Get:7 http://us.archive.ubuntu.com/ubuntu groovy/main amd64 libjson-xs-perl amd64 4.020-1build1 [83.7 kB]
Get:8 http://apt.postgresql.org/pub/repos/apt groovy-pgdg/main amd64 postgresql-client-12 amd64 12.7-1.pgdg20.10+1 [1,429 kB]
Get:9 http://us.archive.ubuntu.com/ubuntu groovy/main amd64 sysstat amd64 12.4.0-1 [471 kB]
Get:10 http://apt.postgresql.org/pub/repos/apt groovy-pgdg/main amd64 postgresql-common all 226.pgdg20.10+1 [246 kB]
Get:11 http://apt.postgresql.org/pub/repos/apt groovy-pgdg/main amd64 postgresql-12 amd64 12.7-1.pgdg20.10+1 [14.9 MB]
Fetched 17.5 MB in 15s (1,141 kB/s) 
Preconfiguring packages ...
Selecting previously unselected package libcommon-sense-perl.
(Reading database ... 192977 files and directories currently installed.)
Preparing to unpack .../00-libcommon-sense-perl_3.75-1build2_amd64.deb ...
Unpacking libcommon-sense-perl (3.75-1build2) ...
Selecting previously unselected package libjson-perl.
Preparing to unpack .../01-libjson-perl_4.02000-2_all.deb ...
Unpacking libjson-perl (4.02000-2) ...
Selecting previously unselected package libtypes-serialiser-perl.
Preparing to unpack .../02-libtypes-serialiser-perl_1.0-1_all.deb ...
Unpacking libtypes-serialiser-perl (1.0-1) ...
Selecting previously unselected package libjson-xs-perl.
Preparing to unpack .../03-libjson-xs-perl_4.020-1build1_amd64.deb ...
Unpacking libjson-xs-perl (4.020-1build1) ...
Selecting previously unselected package libpq5:amd64.
Preparing to unpack .../04-libpq5_13.3-1.pgdg20.10+1_amd64.deb ...
Unpacking libpq5:amd64 (13.3-1.pgdg20.10+1) ...
Selecting previously unselected package pgdg-keyring.
Preparing to unpack .../05-pgdg-keyring_2018.2_all.deb ...
Unpacking pgdg-keyring (2018.2) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../06-postgresql-client-common_226.pgdg20.10+1_all.deb ...
Unpacking postgresql-client-common (226.pgdg20.10+1) ...
Selecting previously unselected package postgresql-client-12.
Preparing to unpack .../07-postgresql-client-12_12.7-1.pgdg20.10+1_amd64.deb ..
.
Unpacking postgresql-client-12 (12.7-1.pgdg20.10+1) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../08-postgresql-common_226.pgdg20.10+1_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by post
gresql-common'
Unpacking postgresql-common (226.pgdg20.10+1) ...
Selecting previously unselected package postgresql-12.
Preparing to unpack .../09-postgresql-12_12.7-1.pgdg20.10+1_amd64.deb ...
Unpacking postgresql-12 (12.7-1.pgdg20.10+1) ...
Selecting previously unselected package sysstat.
Preparing to unpack .../10-sysstat_12.4.0-1_amd64.deb ...
Unpacking sysstat (12.4.0-1) ...
Setting up pgdg-keyring (2018.2) ...
Removing apt.postgresql.org key from trusted.gpg: OK
Setting up libpq5:amd64 (13.3-1.pgdg20.10+1) ...
Setting up libcommon-sense-perl (3.75-1build2) ...
Setting up libtypes-serialiser-perl (1.0-1) ...
Setting up libjson-perl (4.02000-2) ...
Setting up sysstat (12.4.0-1) ...

Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) i
n auto mode
Created symlink /etc/systemd/system/sysstat.service.wants/sysstat-collect.timer
→ /lib/systemd/system/sysstat-collect.timer.
Created symlink /etc/systemd/system/sysstat.service.wants/sysstat-summary.timer
→ /lib/systemd/system/sysstat-summary.timer.
Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /
lib/systemd/system/sysstat.service.
Setting up postgresql-client-common (226.pgdg20.10+1) ...
Setting up libjson-xs-perl (4.020-1build1) ...
Setting up postgresql-client-12 (12.7-1.pgdg20.10+1) ...
update-alternatives: using /usr/share/postgresql/12/man/man1/psql.1.gz to provi
de /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-common (226.pgdg20.10+1) ...
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
en_us
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service 
→ /lib/systemd/system/postgresql.service.
Setting up postgresql-12 (12.7-1.pgdg20.10+1) ...
Creating new PostgreSQL cluster 12/main ...
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local p
eer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/12/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

pg_ctlcluster 12 main start

Ver Cluster Port Status Owner Data directory Log file
12 main 5432 down postgres /var/lib/postgresql/12/main /var/log/postgresq
l/postgresql-12-main.log
update-alternatives: using /usr/share/postgresql/12/man/man1/postmaster.1.gz to
provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for systemd (246.6-1ubuntu1) ...
Processing triggers for man-db (2.9.3-2) ...
Processing triggers for libc-bin (2.32-0ubuntu3) ...
root@PostgreSQL:~# 

Step 5: Verify PostgreSQL Installation

Once the installation is complete, PostgreSQL should start automatically. You can verify this by checking its status using the following command:

systemctl status postgresql.service 

Sample Output:

root@PostgreSQL:~# systemctl status postgresql.service 
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor p>
Active: active (exited) since Sun 2021-05-16 09:17:56 EDT; 1min 56s ago
Main PID: 10932 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 4648)
Memory: 0B
CGroup: /system.slice/postgresql.service

May 16 09:17:56 PostgreSQL systemd[1]: Starting PostgreSQL RDBMS...
May 16 09:17:56 PostgreSQL systemd[1]: Finished PostgreSQL RDBMS.

root@PostgreSQL:~#

Step 6: Connect PostgreSQL 

sudo su - postgres

Step 7: Reset the PostgreSQL password

It is recommended to reset the password to a strong password

psql -c "alter user postgres with password 'StrongAdminP@ssw0rd'"

Sample Output:

root@PostgreSQL:~# sudo su - postgres
postgres@PostgreSQL:~$ 
postgres@PostgreSQL:~$ psql -c "alter user postgres with password 'StrongAdminP@ssw0rd'"
ALTER ROLE
postgres@PostgreSQL:~$

Step 8: Create PostgreSQL Database 

$ psql

$ CREATE DATABASE firsttestdb;

$ CREATE USER testuser1 WITH ENCRYPTED PASSWORD 'MyDBP@ss0rd';

$ GRANT ALL PRIVILEGES ON DATABASE firsttestdb to testuser1;

List the PostgreSQL database:

$\l

How to Connect to PostgreSQL database:

 \c firsttestdb

Sample Output:

postgres=# \c firsttestdb
You are now connected to database "firsttestdb" as user "postgres".
firsttestdb=#

Now, we’ll see how to allow remote connections to PostgreSQL DB.

Step 9: Allow remote connection to PostgreSQL Database

sudo nano /etc/postgresql/12/main/postgresql.conf 

Uncomment line “listen_addresses = ‘*’“

Save the file and restart the PostgreSQL service

sudo systemctl restart postgresql 

Step 10: Check the Listening address

 netstat  -tunelp | grep 5432

Sample Output:

root@PostgreSQL:~# netstat -antple | grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 135 253939 13850/postgres 
tcp6 0 0 :::5432 :::* LISTEN 135 253940 13850/postgres 
root@PostgreSQL:~#

In this tutorial, we have shown you how to install PostgreSQL 12 on Ubuntu 20.04. We have also demonstrated how to configure PostgreSQL to allow remote connections and how to verify the PostgreSQL installation. We hope this tutorial has been helpful to you. If you have any questions or comments, feel free to leave them below.

1. Update and Patch:
   • Regularly update your operating system and PostgreSQL to apply security patches and bug fixes.
2. Change Default Ports:
   • Change the default PostgreSQL port (5432) to a non-standard port to make it less susceptible to automated attacks.
3. Firewall Configuration:
   • Use a firewall to restrict access to the PostgreSQL port. Only allow connections from trusted IP addresses.
4. SSL/TLS Encryption:
   • Enable SSL/TLS encryption to secure data in transit. Ensure that clients connecting to the database use encrypted connections.
5. Strong Authentication:
   • Use strong authentication methods. Avoid using simple or default passwords. Consider implementing certificate-based authentication.
6. Limit Database Users:
   • Only create the necessary database users with the minimum required privileges. Avoid using the default superuser account for routine activities.
7. Host-Based Authentication:
   • Configure pg_hba.conf to specify which hosts are allowed to connect to the PostgreSQL server and what authentication methods are used.
8. Regularly Monitor Logs:
   • Monitor PostgreSQL logs for unusual or suspicious activity. Set up log rotation to prevent logs from consuming excessive disk space.
9. Secure File Permissions:
   • Ensure that only authorized users have read and write access to PostgreSQL files and directories. Follow the principle of least privilege.
10. Regular Backups:
    • Implement a robust backup strategy to ensure data recovery in case of accidental deletion, corruption, or other issues.
11. Security Extensions:
    • Consider using security extensions like pg_stat_statements, pg_audit, or others to monitor and audit database activity.
12. Periodic Security Audits:
    • Conduct periodic security audits to identify and address vulnerabilities. Consider using tools like pg_security_insights for additional security checks.
13. Database Encryption:
    • For an additional layer of security, you can encrypt the database at the storage level using tools like dm-crypt or LUKS.
14. Two-Factor Authentication (2FA):
    • Implement two-factor authentication for sensitive database accounts to add an extra layer of security.
15. Regularly Review Access Controls:
    • Regularly review and update access controls to ensure that only authorized users have access to specific databases and functions.

Always refer to the official PostgreSQL documentation for the most up-to-date and detailed information on securing your PostgreSQL installation. Adjust these practices based on your specific requirements and the sensitivity of your data.

PostgreSQL Backup and Recovery

Implementing a robust backup and recovery strategy is crucial to safeguard your PostgreSQL database and ensure data integrity. Here’s a guide on backup and recovery practices for PostgreSQL 12 on Ubuntu 20.04, 18.04, and 16.04:

Backup Strategies:

1. Full Database Backups:
   • Regularly perform full backups of the entire PostgreSQL database to capture all data, tables, and configurations.
2. Incremental Backups:
   • Consider using incremental backups to reduce backup times and storage requirements. Tools like pg_basebackup and pgBackRest support incremental backups.
3. Automate Backup Jobs:
   • Schedule automated backup jobs using cron or a scheduling tool to ensure regular and consistent backups.
4. Backup Compression:
   • Compress backups to save storage space. PostgreSQL supports compressed backups using tools like pg_dump with the -F option.
5. Offsite Storage:
   • Store backups in an offsite location to protect against disasters. Consider using cloud storage or a dedicated offsite server.
6. Versioned Backups:
   • Keep multiple versions of backups to allow for point-in-time recovery. This is especially useful in scenarios where you need to recover to a specific moment in time.

Recovery Strategies:

1. Test Regularly:
   • Regularly test your backup and recovery procedures in a controlled environment to ensure they work as expected.
2. Point-in-Time Recovery (PITR):
   • Configure and test Point-in-Time Recovery to restore the database to a specific point in time. This is useful for recovering from accidental data deletion or corruption.
3. Continuous Archiving and WAL Shipping:
   • Implement continuous archiving and Write-Ahead Logging (WAL) shipping to maintain a continuous stream of changes, enabling quicker recovery.
4. Monitor WAL Usage:
   • Keep an eye on Write-Ahead Logs to ensure they don’t fill up storage. Implement proper log rotation and archiving.
5. Backup Restoration Process:
   • Document and understand the steps involved in restoring a backup. Ensure that necessary dependencies and configurations are in place.
6. Backup Integrity Checks:
   • Periodically perform integrity checks on your backups to verify that they are not corrupted.
7. Emergency Procedures:
   • Have emergency procedures documented for scenarios where quick recovery is essential. This could involve having a standby server ready for failover.

Tools for Backup and Recovery:

1. pg_dump and pg_restore:
   • Use the built-in pg_dump and pg_restore tools for logical backups.
2. pg_basebackup:
   • For physical backups, consider using pg_basebackup to create a base backup of the entire PostgreSQL cluster.
3. pgBackRest:
   • A powerful tool for managing backup and recovery processes, offering support for both full and incremental backups.
4. Barman:
   • Another popular tool for managing PostgreSQL backups, supporting features like retention policies and compression.

Remember to tailor these strategies based on your specific requirements, such as the size of your database, the acceptable recovery time, and the criticality of your data. Regularly review and update your backup and recovery procedures as your database and business needs evolve.

Basic PostgreSQL commands with an example:

Some common PostgreSQL commands with examples:

1. CREATE DATABASE:

This command is used to create a new database in PostgreSQL.

CREATE DATABASE mydatabase;

2. DROP DATABASE:

This command is used to delete a database in PostgreSQL.

DROP DATABASE mydatabase;

3. CREATE TABLE:

This command is used to create a new table in a PostgreSQL database.

CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL
);

4. DROP TABLE:

This command is used to delete a table in a PostgreSQL database.

DROP TABLE mytable;

5. INSERT INTO:

This command is used to insert data into a table in a PostgreSQL database.

INSERT INTO mytable (name, age)
VALUES ('John', 25);

6. SELECT:

This command is used to retrieve data from a table in a PostgreSQL database.

SELECT * FROM mytable;

7. UPDATE:

This command is used to update data in a table in a PostgreSQL database.

UPDATE mytable SET age = 30 WHERE name = 'John';

8. DELETE:

This command is used to delete data from a table in a PostgreSQL database.

DELETE FROM mytable WHERE name = 'John';

9. CREATE INDEX:

This command is used to create an index on a table in a PostgreSQL database.

CREATE INDEX myindex ON mytable (name);

10. DROP INDEX:

This command is used to delete an index from a table in a PostgreSQL database.

DROP INDEX myindex;

11. ALTER TABLE:

This command is used to modify a table in a PostgreSQL database.

ALTER TABLE mytable ADD COLUMN email VARCHAR(100);

12. CREATE USER:

This command is used to create a new user in a PostgreSQL database.

CREATE USER myuser WITH PASSWORD 'mypassword';

13. DROP USER:

This command is used to delete a user in a PostgreSQL database.

DROP USER myuser;

14. GRANT:

This command is used to grant privileges to a user in a PostgreSQL database.

GRANT SELECT, INSERT, UPDATE, DELETE ON mytable TO myuser;

15. REVOKE:

This command is used to revoke privileges from a user in a PostgreSQL database.

REVOKE SELECT, INSERT, UPDATE, DELETE ON mytable FROM myuser;

16. ALTER USER:

This command is used to modify a user in a PostgreSQL database.

ALTER USER myuser WITH PASSWORD 'newpassword';

17. CREATE SCHEMA:

This command is used to create a new schema in a PostgreSQL database.

CREATE SCHEMA myschema;

18. DROP SCHEMA:

This command is used to delete a schema in a PostgreSQL database.

DROP SCHEMA myschema;

19. SET:

This command is used to set a configuration parameter in a PostgreSQL database.

SET timezone = 'UTC';

20. SHOW:

This command is used to display the value of a configuration parameter in a PostgreSQL database.

SHOW timezone;

These commands should give you a good starting point for working with PostgreSQL. Of course, there are many more commands and options available, so be sure to refer to the official PostgreSQL documentation for more information.

End of the article, we have shown you how to Install PostgreSQL 12 on Ubuntu 20.04/18.04/16.04.

Conclusion

Congratulations! You have successfully installed PostgreSQL 12 on your Ubuntu 20.04 system. You can now start working with this powerful and versatile database management system.

FAQs

Q. Can I install PostgreSQL 12 on other Ubuntu versions?

A. Yes, PostgreSQL 12 can be installed on other Ubuntu versions as well, but the package names and installation process might vary.

Q. Is it necessary to set a password for the “postgres” user?

A. While it’s not mandatory, it is highly recommended to set a password to enhance security.

Q. How can I uninstall PostgreSQL 12 from my system?

A. To uninstall PostgreSQL 12, you can use the following command:

sudo apt remove postgresql-12

Q. Can I have multiple databases under PostgreSQL?

A. Yes, PostgreSQL allows you to create and manage multiple databases on a single server.

Q. Does PostgreSQL support replication?

A. Yes, PostgreSQL supports various replication methods to ensure high availability and data redundancy.

See also:

PostgreSQL commands with examples

How to Install PostgreSQL 12 on Fedora 33

How To Install PostgreSQL 12 on CentOS 7 or RHEL 7

PostgreSQL Blogs

List of monitoring tools 

Linux Blogs

AWS Cloud Blogs

Database Blogs

DevOps Blogs

Interview Questions & Answers

Docker Blogs

Google Cloud Blogs