Mastering PostgreSQL Database: 10 Key Operations for Efficient Management

PostgreSQL Database: A Comprehensive Overview

PostgreSQL, a super secure, object-relational database system, is a part of the most powerful open-source community. Its features that the users can trust are such as ACID compliant behavior, full compliance of the SQL standards with the extensibility of it and its advanced data types. PostgreSQL is a database that is both highly reliable and highly scalable and it’s been used for many years both for small desktop applications and large enterprise systems to store their data.

This manual is a streaming session of essential PostgreSQL Database operations. Simple connections such as connecting to the database, creating a database, selecting a database, and dropping a database are included in this manual as easy operations which are the basis of database applications with PostgreSQL in development.

Key Concepts in PostgreSQL:

• Database is a set of structured data that is arranged in the tables.
• Table is the combination of rows and columns to hold data.
• Schema is a noun that refers to the logical container that houses the tables, indexes, and other database objects.
• Transaction is a series of database operations that are carried out together to ensure the consistency and reliability of the process.

1. Connecting to a PostgreSQL Database

Connecting to a PostgreSQL database is the first step in getting started working with PostgreSQL. On the PostgreSQL database server, there are several ways you can connect to it:

• Using the Command Line Tool (psql)
• Using the Graphical User Interface (GUI) Tool pgAdmin
• Using Programming Languages (JDBC, Python, etc.)

1.1. Connecting Using psql (PostgreSQL Command Line Interface)

• psql is a command-line terminal system that is included with PostgreSQL.
• It makes the user able to do the following: to write SQL queries, proceed through commands on your database, and create and configure PostgreSQL databases and tablespaces without having to use a web-based interface.
• Understand, SQL commands using the CLI interface is a must while one is working with PostgreSQL. Here is how you can connect to a psql.

Step-by-step process:

• Firstly, you open the terminal (Command Line Interface) on your operating system.
• As the psql command to connect to the PostgreSQL database server use the command format:

 psql -h <hostname> -U <username> -d <database_name>

• • -h: Specifies the host of the PostgreSQL server (e.g., localhost for local servers).
  • -U: Specifies the username for the PostgreSQL database user.
  • -d: Specifies the name of the database you want to connect to.
• Example:

To connect to a database named my_database on localhost using the postgres user, run:

psql -h localhost -U postgres -d my_database

• Enter the password for the user postgres when requested;
• After being successfully authenticated, you will be directed to the PostgreSQL interactive terminal. From there, you can start SQL queries.

Basic psql Commands:

• \l − Lists all databases in the PostgreSQL server.
• \c − Decides to connect to a particular database.
• \dt − Lists all tables in the current database.
• \q − Leaves psql terminal.

1.2. Connecting Using pgAdmin

• pgAdmin is a web-based graphical tool that provides a user-friendly interface for managing PostgreSQL databases.
• It is especially beneficial for people who do not want to work with the command line.

Steps to connect using pgAdmin:

1.Launch pgAdmin:

• Open pgAdmin by searching for it in your mouse/pad menu or typing pgAdmin in the command line.

2.Create a new server connection:

• In the pgAdmin interface, right-click the “Servers” node and choose Create → Server.

3.Configure connection settings:

• General Tab: Give a name to the server connection, for example, PostgreSQL_Server.
• Connection Tab: Type in the necessary data:
• Host: For a local PostgreSQL server, use localhost or 127.0.0.1.
• Port: By default, PostgreSQL uses port 5432.
• Username: The default user is typically postgres.
• Password: Give the password to the user.

4.Save the server connection:

• After you enter the connection details, click Save. The server will now be placed in the left panel.

5.Connect to a database:

• Extend the “Servers” node, pick out the server you have just made, and after that, choose the database you would like to connect to.

pgAdmin also has tools available that enable a user to manage tables, schemas, indexes, and other database objects, which makes it perfect for database administrators and developers.

2. Creating a PostgreSQL Database

The database is one of the very first to be set up if we are going to create new projects or applications. On PostgreSQL databases, CREATE DATABASE is a command used to create databases.

2.1. Creating a Database Using SQL

• The CREATE DATABASE SQL statement is a command that creates a new database inside the PostgreSQL server.
• The simple syntax is:

CREATE DATABASE database_name;

• database_name: The name of the new database you wish to create.
• Example:

— Create a database named ‘company_db’

CREATE DATABASE company_db;

2.2. Creating a Database Using psql

To start psql. Use the following command: on the PostgreSQL server:

• Connect to the PostgreSQL server (as shown in the previous section).
• Run the CREATE DATABASE command:

CREATE DATABASE company_db;

• Verify the database creation:
  • After creating the database, you can verify it by running:

\l

This command lists all the PostgreSQL database servers, the company_db name should appear on it. If no, try to create it once more.

2.3. Customizing Database Creation

• PostgreSQL comes with many more options when creating a database.
• For example, encoding, owner, or a template for the database can be set by you.
• Example:

CREATE DATABASE company_db

       WITH OWNER = postgres

       ENCODING =’UTF8′

       TEMPLATE = template0;

• OWNER: Defines the user who is going to own the database.
• ENCODING: Defines the character encoding (e.g., UTF8).
• TEMPLATE: Defines a template database from which to create the new database. template0 is a fresh database without any user-defined objects.

3. Selecting a PostgreSQL Database

The database has to be established first, and then you can connect to it for the needs of creating tables, inserting records, or running various queries.

3.1. Selecting a Database Using SQL

• As PostgreSQL does not support the “USE” operator found in MYSQL, your only alternative is to use the backslash-c feature in the psql terminal to connect to the corresponding database.
• For instance, execute:

— Connect the ‘company_db’ database

\c company_db;

After doing so, the program will link to the company_ db database, and the rest of the commands will be carried out within this context.

3.2. Selecting a Database Using pgAdmin

Selecting a database in pgAdmin is a simple procedure as follows:

• After you have expanded the “Servers” node, you should go to the “Databases” node.
• By proper click to the database “company_db” you needed to connect to, you can choose Connect to which is the first step of the interaction with the database.
• Select Connect to begin displaying info with the database.

4. Dropping a PostgreSQL Database

The DROP DATABASE command is the one to be used to delete a database from the PostgreSQL server. Be aware, this cannot be undone and should be used carefully.

4.1. Dropping a Database Using SQL

• The easiest way to drop a database is to use the DROP DATABASE SQL command:

DROP DATABASE database_name;

• Example:

— Drop the ‘company_db’ database

DROP DATABASE company_db;

• This command permanently wipes out the company_db database together with all the information it stores.

4.2. Dropping a Database Using psql

• Connect to the PostgreSQL server (as already described).
• Disconnect from the database you want to drop, if you are still connected. You should do it after you have connected to another database such as the postgres one, using:

\c postgres;

• Delete the database by issuing:

DROP DATABASE company_db;

Important Notes:

• In case of dropping a database, the operating user must check if there are no other users connected to it. PostgreSQL will not allow dropping of a database that is being actively used.
• If you need  to remove a database that is in use, you need to disconnect the active users. This can be done by terminating the connections to the database with the help of the pg_terminate_backend() function.

4.3. Forcefully Dropping a Database

• To terminate the active connections and drop a database you run the next query that would show connections and you select the one that you want to stop:

— Terminate all connections to the database

SELECT pg_terminate_backend(pid)

FROM pg_stat_activity

WHERE datname = ‘company_db’ AND pid <> pg_backend_pid();

• Then, you can safely drop the database:

DROP DATABASE company_db;

Conclusion

Throughout this guide, we have dealt with in detail how to work with PostgreSQL databases. We covered the main operations and tools for managing PostgreSQL databases in this section:

• Connecting to a PostgreSQL Database:
  • Using the psql command-line tool.
  • Using pgAdmin GUI tool.

• Creating a PostgreSQL Database:
  • Using the CREATE DATABASE SQL command.
  • Customizing database creation with encoding, owner, and templates.

• Selecting a PostgreSQL Database:
  • Using the \c command in psql to connect to a specific database.
  • Selecting a database from pgAdmin.

• Dropping a PostgreSQL Database:
  • Using the DROP DATABASE command.
  • Forcefully terminating active connections to drop a database.

By means of understanding the most basic PostgreSQL actions, you will manage databases effectively in both, development and production, no matter where you are. All the time, remember to the data than before, it drops a data base, perform destructive operations such as drop database and

PostgreSQL is an extremely powerful tool; as well as, learning its features will make you quick in the administration of your project and deploy them at different scale.