Как подключиться к базе данных postgresql через консоль

psql command line tutorial and cheat sheet

You’ve installed PostgreSQL. Now what? I assume you’ve been given a task that uses psql and you want to learn the absolute minimum to get the job done.

This is both a brief tutorial and a quick reference for the absolute least you need to know about psql . I assume you’re familiar with the command line and have a rough idea about what database administration tasks, but aren’t familiar with how to use psql to do the basics.

View on GitHub Pages or directly on GitHub

The PostgreSQL documentation is incredibly well written and thorough, but frankly, I didn’t know where to start reading. This is my answer to that problem.

If you have any complaints or suggestions please let me know by sending your feedback to tomcampbell@gmail.com.

It shows how to do the following at the psql prompt:

If you don’t have access to a live PostgreSQL installation at the moment we still have your back. You can follow through the examples and the output is shown as if you did type everything out.

The psql command line utility

Many administrative tasks can or should be done on your local machine, even though if database lives on the cloud. You can do some of them through a visual user interface, but that’s not covered here. Knowing how to perform these operations on the command line means you can script them, and scripting means you can automate tests, check errors, and do data entry on the command line.

This section isn’t a full cheat sheet for psql . It covers the most common operations and shows them roughly in sequence, as you’d use them in a typical work session.

Starting and quitting the psql interactive terminal
Command-line prompts for psql
Quitting psql
Opening a connection locally
Opening a connection remotely
Using the psql prompt
Getting information about databases
\h Help
\l List databases
\c Connect to a database
\dt Display tables
\d and \d+ Display columns (field names) of a table
\du Display user roles
Creating and using tables and records
Creating a database
Creating a table (CREATE TABLE)
Adding a record (INSERT INTO)
Inserting several records at once (INSERT INTO)
Adding only specific fields from a record
Doing a simple query–get a list of records (SELECT)
Maintenance and operations
Timing
Watch
Maintenance

What you need to know

Before using this section, you’ll need:

• The user name and password for your PostgreSQL database
• The IP address of your remote instance

Command-line prompts on the operating system

The $ starting a command line in the examples below represents your operating system prompt. Prompts are configurable so it may well not look like this. On Windows it might look like C:\Program Files\PostgreSQL> but Windows prompts are also configurable.

A line starting with # represents a comment. Same for everything to the right of a # . If you accidentally type it or copy and paste it in, don’t worry. Nothing will happen.

Using psql

You’ll use psql (aka the PostgreSQL interactive terminal) most of all because it’s used to create databases and tables, show information about tables, and even to enter information (records) into the database.

Quitting pqsql

Before we learn anything else, here’s how to quit psql and return to the operating system prompt. You type backslash, the letter q , and then you press the Enter or return key.

This takes you back out to the operating system prompt.

Opening a connection locally

A common case during development is opening a connection to a local database (one on your own machine). Run psql with -U (for user name) followed by the name of the database, postgres in this example:

Opening a connection remotely

To connect your remote PostgreSQL instance from your local machine, use psql at your operating system command line. Here’s a typical connection.

Here you’d enter the password. In case someone is peering over your shoulder, the characters are hidden. After you’ve entered your information properly you’ll get this message (truncated for clarity):

Looking at the psql prompt

A few things appear, then the psql prompt is displayed. The name of the current database appears before the prompt.

At this point you’re expected to type commands and parameters into the command line.

psql vs SQL commands

psql has two different kinds of commands. Those starting with a backslash are for psql itself, as illustrated by the use of \q to quit.

Those starting with valid SQL are of course interactive SQL used to create and modify PostgreSQL databases.

Warning: SQL commands end with a semicolon!

One gotcha is that almost all SQL commands you enter into psql must end in a semicolon.

• For example,suppose you want to remove a table named sample_property_5 . You’d enter this command:

It’s easy to forget. If you do forget the semicolon, you’ll see this perplexing prompt. Note that a [ has been inserted before the username portion of the prompt, and another prompt appears below it:

When you do, just remember to finish it off with that semicolon:

Scrolling through the command history

• Use the up and down arrow keys to move backwards and forwards through the command history.

Getting information about databases

These aren’t SQL commands so just press Enter after them. Remember that:

• When there’s more output than fits the screen, it pauses. Press space to continue
• If you want to halt the output, press q .

\h Help

You’ll get a long list of commands, then output is paused:

• Press space to continue, or q to stop the output.

You can get help on a particular item by listing it after the \h command.

• For example, to get help on DROP TABLE :

You’ll get help on just that item:

\l List databases

What most people think of as a database (say, a list of customers) is actually a table. A database is a set of tables, information about those tables, information about users and their permissions, and much more. Some of these databases (and the tables within) are updated automatically by PostgreSQL as you use them.

To get a list of all databases:

You can get info on a single database by following the \l prompt with its name.

• For example, to view information about the template0 database:

The output would be:

\l+ List databases with size, tablespace, and description

To get additional information on the space consumed by database tables and comments describing those tables, use \l+ :

\x Expand/narrow table lists

Use \x (X for eXpanded listing) to control whether table listings use a wide or narrow format.

Command	Effect
\x off	Show table listings in wide format
\x on	Show table listings in narrow format
\x	Reverse the previous state
\x auto	Use terminal to determine format

Example: Here’s an expanded listing:

Use \x on for narrower listings:

\c Connect to a database

To see what’s inside a database, connect to it using \c followed by the database name. The prompt changes to match the name of the database you’re connecting to. (The one named postgres is always interesting.) Here we’re connecting to the one named markets :

\dt Display tables

• Use \dt to list all the tables (technically, relations) in the database:

• If you choose a database such as postgres there could be many tables. Remember you can pause output by pressing space or halt it by pressing q .

\d and \d+ Display columns (field names) of a table

To view the schema of a table, use \d followed by the name of the table.

• To view the schema of a table named customerpaymentsummary , enter

To view more detailed information on a table, use \d+ :

\du Display user roles

• To view all users and their roles, use \du :

• To view the role of a specific user, pass it after the \du command. For example, to see the only tom role:

Creating a database

Before you add tables, you need to create a database to contain those tables. That’s not done with psql , but instead it’s done with createdb (a separate external command; see the PostgreSQL createdb documentation) at the operating system command line:

On success, there is no visual feedback. Thanks, PostgreSQL.

Adding tables and records

Creating a table (CREATE TABLE)

To add a table schema to the database:

And psql responds with:

For more see CREATE TABLE in the PostgreSQL official docs.

Adding a record (INSERT INTO)

• Here’s how to add a record, populating every field:

PostgreSQL responds with:

• Try it again and you get a simliar response.

Adding (inserting) several records at once

• You can enter a list of records using this syntax:

Adding only specific (columns) fields from a record

You can add records but specify only selected fields (also known as columns). MySQL will use common sense default values for the rest.

In this example, only the name field will be populated. The sku column is left blank, and the id column is incremented and inserted.

Two records are added:

PostgreSQL responds with the number of records inserted:

For more on INSERT, see INSERT in the PostgreSQL official docs

Doing a simple query–get a list of records (SELECT)

Probably the most common thing you’ll do with a table is to obtain information about it with the SELECT statement. It’s a huge topic

• Let’s list all the records in the product table:

If your table has mixed case objects such as column names or indexes, you’ll need to enclose them in double quotes. For example, If a column name were Product instead of product your query would need to look like this:

For more on SELECT, see the SELECT in the PostgreSQL official docs.

Maintenance and operations issues

Timing

\t Timing SQL operations

Use \t to show timing for all SQL operations performed.

Command	Effect
\timing off	Disable timing of SQL operations
\timing on	Show timing after all SQL operations
\timing	Toggle (reverse) the setting

Example of \t Timing command

Watch

The \watch command repeats the previous command at the specified interval. To use it, enter the SQL command you want repeated, then use \watch followed by the number of seconds you want for the interval between repeats, for rexample, \watch 1 to repeat it every second.

Example of the \Watch command

Here’s an example of using \watch to see if any records have been inserted within the last 5 seconds.

Работаем с PostgreSQL через командную строку в Linux

Для подключения к базе данных PostgreSQL понадобится установленный PostgreSQL клиент:

Для установки PostgreSQL сервера:

Проверим, можем ли мы подключиться к базе данных PostgreSQL:

Вывод команды должен быть примерно таким:

PostgreSQL Подключение, Пользователи (Роли) и Базы Данных

Логин в только что установленный postgreSQL сервер нужно производить под именем пользователя postgres:

Для подключения к базе данных PostgreSQL можно использовать команду:

Если такая команда не просит ввести пароль пользователя, то можно еще добавить опцию -W.

После ввода пароля и успешного подключения к базе данных PostgreSQL, можно посылать SQL-запросы и psql-команды.

PostgreSQL создание новой роли и базы данных

Создать новую роль c именем admin (указывайте нужное имя):

Создание новой базы данных:

Дать права роли на базу данных:

Включить удаленный PostgreSQL доступ для пользователей

Нам нужно отредактировать файл /etc/postgresql/<VERSION>/main/pg_hba.conf, задав опцию md5 вместо peer.

<VERSION> может быть 10, 11, 12 и т.д.

После этого сделать restart PostgreSQL:

Полезные команды PostgreSQL

Выйти из клиента PostgreSQL:

\q

Показать список баз данных PostgreSQL:

\l

Показать список таблиц:

\dt

Показать список пользователей (ролей):

\du

Показать структуру таблицы:

Переименовать базу данных:

Удалить базу данных:

Изменить текущую базу данных в PostgreSQL (вы не сможете переименовать или удалить текущую базу данных):

\connect db_name или более короткий alias: \c db_name

Удалить роль (пользователя):

Роль не будет удалена, если у нее есть привелегии — возникнет ошибка ERROR: role cannot be dropped because some objects depend on it .

Нужно удалить привелегии у роли, например если нужно удалить роль admin2, нужно выполнить последовательность комманд с Drop Owned:

Дать права пользователю/роли на логин ( role is not permitted to log in ):

Выбор shema psql в консоли:

Посмотреть список всех схем:

Подключиться к конкретной схеме:

Sequences

Получить имена всех созданных sequences:

Получить последнее значение sequence, которые будет присвоено новой вставляемой в таблицу записи:

Как подключиться к базе данных postgresql через консоль

Консольный клиент psql представляет еще один способ взаимодействия с сервером PostgreSQL. Данная программа также, как и pgAdmin, позволяет выполнять команды языка SQL.

Запустим psql. Программа предложит ввести название сервера, базы данных, порта и пользователя. Эти пункты можно прощелкать, так как для них будут использоваться значения по умолчанию (для сервера — localhost, для базы данных — postgres, для порта — 5432, в качестве пользователя — суперпользователь postres). Далее надо будет ввести пароль для пользователя (по умолчанию пользователя postgres):

И после удачного подключения можно будет отправлять серверу команды через psql.

Теперь создадим базу данных с помощью следующей команды языка SQL:

Для создания базы данных применяется команда create database , после которой указывается название бд. То есть в данном случае название бд — «test2». Причем команда завершается точкой с запятой.

Далее подключимся к этой базе данных для осуществления с ней взаимодействия. Для этого применяется команда \c (сокращение от connect), после которой указывается имя базы данных:

Затем создадим в этой базе данных таблицу с помощью команды:

Данная команда создает таблицу users, в которой будет три столбца — Id, Name и Age.

После этого мы можем добавлять и получать данные из выше созданной таблицы. Вначале добавим в таблицу одну строку с помощью следующей команды:

И в конце получим добавленные данные:

Стоит отметить, что по умолчанию консоль в Windows поддерживает кодировку CP866, тогда как базы данных могут работать совсем с другой кодировкой, например, 1251. И даже сам клиент psql выводит нам соответствующие сообщения. Кроме того, при получении данных, при выводе информации о базах данных, таблицы и т.д. некоторая информация может отображаться некорректно. В этом случае перед запуском psql надо установить нужную кодировку и затем из консоли запустить программу psql.

How to Manage PostgreSQL Databases from the Command Line with psql

Gerard Hynes

Now is a great time to learn relational databases and SQL. From web development to data science, they are used everywhere.

In the Stack Overflow 2021 Survey, 4 out of the top 5 database technologies used by professional developers were relational database management systems.

PostgreSQL is an excellent choice as a first relational database management system to learn.

1. It’s widely used in industry, including at Uber, Netflix, Instagram, Spotify, and Twitch.
2. It’s open source, so you won’t be locked into a particular vendor.
3. It’s more than 25 years old, and in that time it has earned a reputation for stability and reliability.

Whether you’re learning from the freeCodeCamp Relational Database Certification or trying out PostgreSQL on your own computer, you need a way to create and manage databases, insert data into them, and query data from them.

While there are several graphical applications for interacting with PostgreSQL, using psql and the command line is probably the most direct way to communicate with your database.

What is psql?

psql is a tool that lets you interact with PostgreSQL databases through a terminal interface. When you install PostgreSQL on a machine, psql is automatically included.

psql lets you write SQL queries, send them to PostgreSQL, and view the results. It also lets you use meta-commands (which start with a backslash) for administering the databases. You can even write scripts and automate tasks relating to your databases.

Now, running a database on your local computer and using the command line can seem intimidating at first. I’m here to tell you it’s really not so bad. This guide will teach you the basics of managing PostgreSQL databases from the command line, including how to create, manage, back up, and restore databases.

Prerequisite – Install PostgreSQL

If you haven’t already installed PostgreSQL on your computer, follow the instructions for your operating system on the official PostgreSQL documentation.

When you install PostgreSQL, you will be asked for a password. Keep this in a safe place as you’ll need it to connect to any databases you create.

How to Connect to a Database

You have two options when using psql to connect to a database: you can connect via the command line or by using the psql application. Both provide pretty much the same experience.

Option 1 – Connect to a database with the command line

Open a terminal. You can make sure psql is installed by typing psql —version . You should see psql (PostgreSQL) version_number , where version_number is the version of PostgreSQL that’s installed on your machine. In my case, it’s 14.1.

Checking psql version via the command line

The pattern for connecting to a database is:

The -d flag is shorter alternative for —dbname while -U is an alternative for —username .

When you installed PostgreSQL, a default database and user were created, both called postgres . So enter psql -d postgres -U postgres to connect to the postgres database as the postgres superuser.

You will be prompted for a password. Enter the password you chose when you installed PostgreSQL on your computer. Your terminal prompt will change to show that you’re now connected to the postgres database.

Connecting to a database from the command line with psql

If you want to directly connect to a database as yourself (rather than as the postgres superuser), enter your system username as the username value.

Option 2 – Connect to a database with the psql application

Launch the psql application – it’ll be called «SQL Shell (psql)». You will be prompted for a server, a database, a port and a username. You can just press enter to select the default values, which are localhost , postgres , 5432 , and postgres .

Next, you’ll be prompted for the password you chose when you installed PostgreSQL. Once you enter this, your terminal prompt will change to show that you’re connected to the postgres database.

Connecting to a database with the psql application

Note: If you’re on Windows you might see a warning like “Console code page (850) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page ‘Notes for Windows users’ for details.” You don’t need to worry about this at this stage. If you want to read more about it, see the psql documentation.

How to Get Help in psql

To see a list of all psql meta-commands, and a brief summary of what they do, use the \? command.

psql’s help command

If you want help with a PostgreSQL command, use \h or \help and the command.

This will give you a description of the command, its syntax (with optional parts in square brackets), and a URL for the relevant part of the PostgreSQL documentation.

psql describing the DROP TABLE statement

How to Quit a Command in psql

If you’ve run a command that’s taking a long time or printing too much information to the console, you can quit it by typing q .

How to Create a Database

Before you can manage any databases, you’ll need to create one.

Note: SQL commands should end with a semicolon, while meta-commands (which start with a backslash) don’t need to.

The SQL command to create a database is:

For this guide, we’re going to be working with book data, so let’s create a database called books_db .

How to List Databases

You can view a list of all available databases with the list command.

Listing all databases

You should see books_db , as well as postgres , template0 , and template1 . (The CREATE DATABASE command actually works by copying the standard database, called template1 . You can read more about this in the PostgreSQL documentation.)

Using \l+ will display additional information, such as the size of the databases and their tablespaces (the location in the filesystem where the files representing the database will be stored).

Listing all databases with additional information

How to Switch Databases

You’re currently still connected to the default postgres database. To connect to a database or to switch between databases, use the \c command.

So \c books_db will connect you to the books_db database. Note that your terminal prompt changes to reflect the database you’re currently connected to.

Switching databases

How to Delete a Database

If you want to delete a database, use the DROP DATABASE command.

You will only be allowed to delete a database if you are a superuser, such as postgres , or if you are the database’s owner.

If you try to delete a database that doesn’t exist, you will get an error. Use IF EXISTS to get a notice instead.

Deleting a database

You can’t delete a database that has active connections. So if you want to delete the database you are currently connected to, you’ll need to switch to another database.

How to Create Tables

Before we can manage tables, we need to create a few and populate them with some sample data.

The command to create a table is:

This will create an empty table. You can also pass column values into the parentheses to create a table with columns. At the very least, a basic table should have a Primary Key (a unique identifier to tell each row apart) and a column with some data in it.

For our books_db , we’ll create a table for authors and another for books. For authors, we’ll record their first name and last name. For books, we’ll record the title and the year they were published.

We’ll make sure that the authors’ first_name and last_name and the books’ title aren’t null, since this is pretty vital information to know about them. To do this we include the NOT NULL constraint.

You will see CREATE TABLE printed to the terminal if the table was created successfully.

Now let’s connect the two tables by adding a Foreign Key to books. Foreign Keys are unique identifiers that reference the Primary Key of another table. Books can, of course, have multiple authors but we’re not going to get into the complexities of many to many relationships right now.

Add a Foreign Key to books with the following command:

Next, let’s insert some sample data into the tables. We’ll start with authors .

Select everything from authors to make sure the insert command worked.

Querying all data from the authors table

Next, we’ll insert some books data into books .

If you run SELECT * FROM books; you’ll see the book data.

Querying all data from the books table

How to List All Tables

You can use the \dt command to list all the tables in a database.

For books_db you will see books and authors . You’ll also see books_book_id_seq and authors_author_id_seq . These keep track of the sequence of integers used as ids by the tables because we used SERIAL to generate their Primary Keys.

Listing all tables in a database

How to Describe a Table

To see more information about a particular table, you can use the describe table command: \d table_name . This will list the columns, indexes, and any references to other tables.

Describing the authors table

Using \dt+ table_name will provide more information, such as about storage and compression.

How to Rename a Table

If you ever need to change the name of a table, you can rename it with the ALTER TABLE command.

How to Delete a Table

If you want to delete a table, you can use the DROP TABLE command.

If you try to delete a table that doesn’t exist, you will get an error. You can avoid this by including the IF EXISTS option in the statement. This way you’ll get a notice instead.

How to Manage Longer Commands and Queries

If you’re writing longer SQL queries, the command line isn’t the most ergonomic way to do it. It’s probably better to write your SQL in a file and then have psql execute it.

If you are working with psql and think your next query will be long, you can open a text editor from psql and write it there. If you have an existing query, or maybe want to run several queries to load sample data, you can execute commands from a file that is already written.

Option 1 – Open a text editor from psql

If you enter the \e command, psql will open a text editor. When you save and close the editor, psql will run the command you just wrote.

Writing commands in a text editor

On Windows, the default text editor for psql is Notepad, while on MacOs and Linux it’s vi. You can change this to another editor by setting the EDITOR value in your computer’s environment variables.

Option 2 – Execute commands and queries from a file

If you have particularly long commands or multiple commands that you want to run, it would be better to write the SQL in a file ahead of time and have psql execute that file once you’re ready.

The \i command lets you read input from a file as if you had typed it into the terminal.

Note: If you’re executing this command on Windows, you still need to use forward slashes in the file path.

If you don’t specify a path, psql will look for the file in the last directory that you were in before you connected to PostgreSQL.

Executing SQL commands from a file

How to Time Queries

If you want to see how long your queries are taking, you can turn on query execution timing.

This will display in milliseconds the time that the query took to complete.

If you run the \timing command again, it will turn off query execution timing.

Using query execution timing

How to Import Data from a CSV File

If you have a CSV file with data and you want to load this into a PostgreSQL database, you can do this from the command line with psql.

First, create a CSV file called films.csv with the following structure (It doesn’t matter if you use Excel, Google Sheets, Numbers, or any other program).

A spreadsheet with Pixar film data

Open psql and create a films_db database, connect to it, and create a films table.

You can then use the \copy command to import the CSV file into films . You need to provide an absolute path to where the CSV file is on your computer.

The DELIMITER option specifies the character that separates the columns in each row of the file being imported, CSV specifies that it is a CSV file, and HEADER specifies that the file contains a header line with the names of the columns.

Note: The column names of the films table don’t need to match the column names of films.csv but they do need to be in the same order.

Use SELECT * FROM films; to see if the process was successful.

Importing data from a .csv file

How to Back Up a Database with pg_dump

If you need to backup a database, pg_dump is a utility that lets you extract a database into a SQL script file or other type of archive file.

First, on the command line (not in psql), navigate to the PostgreSQL bin folder.

Then run the following command, using postgres as the username, and filling in the database and output file that you want to use.

Use postgres for the username and you will be prompted for the postgres superuser’s password. pg_dump will then create a .sql file containing the SQL commands needed to recreate the database.

Backing up a database to a .sql file

If you don’t specify a path for the output file, pg_dump will save the file in the last directory that you were in before you connected to PostgreSQL.

Contents of films.sql backup file

You can pass the -v or —verbose flag to pg_dump to see what pg_dump is doing at each step.

Running pg_dump in verbose mode

You can also backup a database to other file formats, such as .tar (an archive format).

Here the -F flag tells pg_dump that you’re going to specify an output format, while t tells it it’s going to be in the .tar format.

How to Restore a Database

You can restore a database from a backup file using either psql or the pg_restore utility. Which one you choose depends on the type of file you are restoring the database from.

1. If you backed up the database to a plaintext format, such as .sql , use psql.
2. If you backed up the database to an archive format, such as .tar , use pg_restore .

Option 1 – Restore a database using psql

To restore a database from a .sql file, on the command line (so not in psql), use psql -U username -d database_name -f filename.sql .

You can use the films_db database and films.sql file you used earlier, or create a new backup file.

Create an empty database for the file to restore the data into. If you’re using films.sql to restore films_db , the easiest thing might be to delete films_db and recreate it.

In a separate terminal (not in psql), run the following command, passing in postgres as the username, and the names of the database and backup file you are using.

The -d flag points psql to a specific database, while the -f flag tells psql to read from the specified file.

If you don’t specify a path for the backup file, psql will look for the file in the last directory that you were in before you connected to PostgreSQL.

You will be prompted for the postgres superuser’s password and then will see a series of commands get printed to the command line while psql recreates the database.

Restoring a database using psql

This command ignores any errors that occur during the restore. If you want to stop restoring the database if an error occurs, pass in —set ON_ERROR_STOP=on .

Option 2 – Restore a database using pg_restore

To restore a database using pg_restore , use pg_restore -U username -d database_name path_to_file/filename.tar .

Create an empty database for the file to restore the data into. If you’re restoring films_db from a films.tar file, the easiest thing might be to delete films_db and recreate it.

On the command line (not in psql), run the following command, passing in postgres as the username, and the names of the database and backup file you are using.

Restoring a database using pg_restore

You can also pass in the -v or —verbose flag to see what pg_restore is doing at each step.

Using pg_restore in verbose mode

How to Quit psql

If you’ve finished with psql and want to exit from it, enter quit or \q .

This will close the psql application if you were using it, or return you to your regular command prompt if you were using psql from the command line.

Where to Take it from Here

There are lots more things you can do with psql, such as managing schemas, roles, and tablespaces. But this guide should be enough to get you started with managing PostgreSQL databases from the command line.

If you want to learn more about PostgreSQL and psql, you could try out freeCodeCamp’s Relational Database Certificate . The official PostgreSQL documentation is comprehensive, and PostgreSQL Tutorial offers several in-depth tutorials.

I hope you find this guide helpful as you continue to learn about PostgreSQL and relational databases.