PostgreSQL command-line cheat sheet

Overview of PostgreSQL Command-Line Tools

PostgreSQL is one of the most popular open-source relational database management systems, and mastering the command-line interface can empower you to be more efficient in database management. This article is a cheat sheet that guides you through several powerful command-line tools and common commands needed for operating PostgreSQL effectively.

Installation & Setup

Before you start using PostgreSQL, you need to have it installed. This can usually be done via your OS package manager. Here’s a sample installation command for Ubuntu:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Please ensure that you have administrative privileges to install and manage services on your system.

Starting and Stopping the Service

Once PostgreSQL is installed, the service can be managed using the system's service control commands. For instance:

sudo service postgresql start   # Start PostgreSQL
sudo service postgresql stop    # Stop PostgreSQL
sudo service postgresql restart # Restart PostgreSQL

Accessing PostgreSQL CLI

The main command-line tool for PostgreSQL is psql, which allows you to interact with PostgreSQL databases. To log into a database, use the following command:

psql -U username -d dbname

The above command connects you to the specified database using the provided username. Once you're inside the psql shell, you can type various SQL commands or use specific psql commands prefixed by a backslash to manage databases and roles.

Basic SQL Commands

The following SQL commands are essential for everyday database operations:

SELECT * FROM table_name;          -- Retrieves all records from a table
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE table_name SET column1 = value1 WHERE condition;
DELETE FROM table_name WHERE condition;

These commands are crucial for manipulating and retrieving data from your PostgreSQL databases.

Using psql Meta-Commands

Within the psql shell, meta-commands provide powerful means of database interactions. Some useful commands include:

\l        # Lists all databases
\c dbname  # Connects to a specified database
\dt       # Lists all tables in the current database
\d tablename  # Shows descriptions of a specified table

To exit the PostgreSQL command-line interface, simply type \q.

Backup and Restore

Backing up data is critical for disaster recovery and maintenance. PostgreSQL provides a utility called pg_dump for exporting databases to a file, which can later be restored using psql or pg_restore for more complex cases:

pg_dump dbname > backupfile.sql             # Create a backup
psql dbname < backupfile.sql                # Restore a backup
pg_dump -Fc dbname > db.dump                # Custom format for backups
pg_restore -d dbname db.dump                # Restore from custom format

Managing Roles and Permissions

The PostgreSQL database management system uses predefined roles for access management, which means different users can be granted or revoked privileges. Essential commands include:

CREATE ROLE new_user WITH LOGIN PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE dbname TO new_user;
REVOKE ALL PRIVILEGES ON DATABASE dbname FROM existing_user;

Conclusion

The PostgreSQL command-line tools empower database administrators to effectively interact with and manage databases. This cheat sheet is designed to give you an accessible reference for common PostgreSQL tasks. Practice regularly, and you'll find that manipulating PostgreSQL databases via the command line becomes second nature.