PostgreSQL Commands
This document explains the installation and configuration of the PostgreSQL database engine.
Install PostgreSQL
Change the listening port
In case you need to change the listening port (for example when I had to test postgresql on docker containers while keeping postgresql running locally) Edit the configuration file /etc/postgresql/13/main/postgresql.conf and change the port definition as follows
Start and stop the server
Create a user and a database
Add system users at the regular bash shell before you can add them in postgresql. I call this user “rdb” because I plan to use R to connect to the database and “R” cannot be used as a user name according to system policy.
Login as the postgres user!
Now logged in as postgres@machine_name in the regular bash shell:
Create a user with the same name as the system user created above
createuser –pwprompt rdb # Create a user for myself createuser paul
Create a database owned by that user
createdb -O rdb tradeflows createdb -O rdb biotrade createdb -O paul tradeflows_migrated
Note: createdb is a wrapper around the SQL command CREATE DATABASE . At the postgreSQL command prompt, you can use
Grant rights to another user. First connect to the database
Then grand privileges
Connect to a database
Configuration file
This is the preferred way to connect to the database for scripts. Create a
/.pgpass file to store the connection details for that user:
Secure the file:
/.pgpass file should contain lines of the following format:
“Each of the first four fields can be a literal value, or * , which matches anything.”
Therefore I use * for the port so that it matches any ports.
Service file
The postgresql service file enables “connection parameters to be associated with a single service name”. You can specify the database connection parameters in a file called
/.pg_service.conf in you home.
And you would then connect to the database with:
Connect to the database
Using the credentials stored in
Specifying the port (normally not necessary if it’s specified in
It’s possible to specify a connection URI of the form:
“It is possible to specify multiple host components, each with an optional port component, in a single URI. A URI of the form postgresql://host1:port1,host2:port2,host3:port3/ is equivalent to a connection string of the form host=host1,host2,host3 port=port1,port2,port3. As further described below, each host will be tried in turn until a connection is successfully established.”
Connect by changing the user at the system level
Login as the user who is the owner of that database
Connect to the database
Data types
Character
| Name | Description |
|---|---|
| character varying(n), varchar(n) | variable-length with limit |
| character(n), char(n) | fixed-length, blank padded |
| text | variable unlimited length |
“There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.”
“Generally, there is no downside to using text in terms of performance/memory. On the contrary: text is the optimum. Other types have more or less relevant downsides. text is literally the”preferred” type among string types in the Postgres type system, which can affect function or operator type resolution.”
To sum it all up: — char(n) – takes too much space when dealing with values shorter than n (pads them to n), and can lead to subtle errors because of adding trailing spaces, plus it is problematic to change the limit — varchar(n) – it’s problematic to change the limit in live environment (requires exclusive lock while altering table) — varchar – just like text — text – for me a winner – over (n) data types because it lacks their problems, and over varchar – because it has distinct name
In SQLite as well, character(n) doesn’t seem to have any performance advantage.
Note that numeric arguments in parentheses that following the type name (ex: “VARCHAR(255)”) are ignored by SQLite — SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.
Delete data
Delete content from a table
Keep the table structure but delete it’s content. Delete oil products from the Comtrade monthly table:
Delete a database
Delete a schema
Delete a schema and all its table with a drop in cascade
Examples copied from man pg_dump . To dump a database called mydb into a SQL-script file:
To reload such a script into a (freshly created) database named newdb:
Remove statements
The dump contained the following two statements which I removed:
I removed default table space because the official documentation for table spaces says it doesn’t make sense to have more than one table space per system.
I removed default_with_oids because this posrt explains that it’s used to enable a legacy feature.
What's the difference between sudo su — postgres and sudo -u postgres?
PostgreSQL users peer authentication on unix sockets by default, where the unix user must be the same as the PostgreSQL user. So people frequently use su or sudo to become the postgres superuser.
I often see people using constructs like:
and I’m wondering why. Similarly, I’ve seen:
Without the leading sudo the su versions would make some sense if you were on an old platform without sudo . But why on a less than prehisoric UNIX or Linux would you use sudo su ?
1 Answer 1
Forget sudo su
There is no benefit to using sudo su , it’s an anachronistic habit from when people were used to using su . People started tacking sudo in front when Linux distros stopped setting a root password and made sudo the only way to access the root account. Rather than change their habits, they just used sudo su . (I was one of them until relatively recently when using boxes with sudoers configs forced me to change my habit).
Use sudo -u
For a login shell, sudo -u postgres -i is preferable to sudo su — postgres . It doesn’t require that the user have root access in /etc/sudoers , they only need the right to become user postgres . It also lets you enforce better access controls.
For command execution
is superior to the alternative:
in that you don’t have to double-escape quotes and other shell metacharacters as well as the other security advantages of not needing root. You’ll probably accidentally land up writing:
sometimes, which won’t work properly.
Finally, it’s way easier to set environment variables via sudo , e.g.
than via su . (Here, the PATH setting is required so that initdb can find the correct postgres executable).
So. Forget the su command exists. You don’t need it anymore. To break the habit, alias it to something that’ll print an error. (Some init and package setup scripts still use su so you can’t remove it, though).
Работаем с 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 в Ubuntu
Реляционные системы управления базами данных (РСУБД) — это ключевой компонент многих веб-сайтов и приложений. Они обеспечивают структурированный способ хранения данных и организацию доступа к информации. PostgreSQL- это объектно-реляционная система управления базами данных, которая все больше и больше вытесняет MySQL и производственных серверов.
Её преимущество в множестве дополнительных функций и улучшений, таких как надежная передача данных и параллелизация без блокировок чтения. Вы можете использовать эту СУБД с различными языками программирования, а её синтаксис запросов PL/pgSQL очень похож на MySQL от Oracle. В этой статье мы рассмотрим, как выполняется установка PostgreSQL в Ubuntu 20.04 из официальных репозиториев и репозитория PostgreSQL (PPA) а так же, как выполнить первоначальную настройку и подготовку к работе c данной СУБД.
Установка PostgreSQL в Ubuntu 20.04
1. Установка из официальных репозиториев
Это очень популярная СУБД, потому программа присутствует в официальных репозиториях. Для установки выполните следующие команды. Сначала обновите списки пакетов:
sudo apt update
Установите СУБД PostgreSQL:
sudo apt -y install postgresql
2. Установка из официальных репозиториев PostgreSQL
Если есть необходимость в получение самой последней версии, то необходимо добавить в систему официальный PPA от разработчиков PostgreSQL. Для этого выполните следующие команды:
sudo sh -c ‘echo «deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main» > /etc/apt/sources.list.d/pgdg.list’
wget —quiet -O — https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add —
Далее обновите списки пакетов, чтобы получить самую новую доступную версию:
sudo apt update
Установка PostgreSQL из PPA или официальных репозиториев выглядит одинаково:
sudo apt -y install postgresql
Настройка PostgreSQL в Ubuntu 20.04
После установки СУБД откройте терминал и переключитесь на пользователя postgres с помощью команды:
sudo -i -u postgres
Эта учетная запись создается во время установки программы и на данный момент вы можете получить доступ к системе баз данных только с помощью нее. По умолчанию PostgreSQL использует концепцию ролей для аутентификации и авторизации.
Это очень похоже на учетные записи Unix, но программа не различает пользователей и групп, есть только роли. Сразу после установки PostgreSQL пытается связать свои роли с системными учетными записями, если для имени системной учетной записи существует роль, то пользователь может войти в консоль управления и выполнять позволенные ему действия. Таким образом, после переключения на пользователя postgres вы можете войти в консоль управления:
И посмотреть информацию о соединении:
Чтобы выйти наберите:
Теперь рассмотрим, как создать другие роли и базы данных.
Создание роли postgresql
Вы уже можете полноценно работать с базой данных с помощью учетной записи postgres, но давайте создадим дополнительную роль. Учетная запись postgres является администратором, поэтому имеет доступ к функциям управления. Для создания пользователя выполните команду:
Скрипт задаст лишь два вопроса, имя новой роли и нужно ли делать ее суперпользователем.
Создание базы данных
Точно также как имена ролей сопоставляются с системными пользователями, имя базы данных будет подбираться по имени пользователя. Например, если мы создали пользователя alex, то по умолчанию система попытается получить доступ к базе данных alex. Мы можем ее очень просто создать:
Дальше, чтобы подключиться к этой базе данных нам нужно войти от имени одноименного пользователя:
Заходим в консоль и смотрим информацию о подключении:
Все верно сработало. Мы подключились с помощью роли alex к базе alex. Если нужно указать другую базу данных, вы можете сделать это с помощью опции -d, например:
psql -d postgres
Все сработало верно, при условии, что все компоненты были настроены как описано выше.
Создание таблиц
Теперь, когда вы знаете, как подключится к базе данных PostgreSQL, давайте рассмотрим, как выполняются основные задачи. Сначала разберем создание таблиц для хранения некоторых данных. Для создания таблицы PostgreSQLиспользуется такой синтаксис:
CREATE TABLE имя_таблицы (имя_колонки1 тип_колонки (длина) ограничения, имя_колонки2 тип_колонки (длина), имя_колонки3 тип_колонки (длина));
Как видите, сначала мы задаем имя таблицы, затем описываем каждый столбец. Столбец должен иметь имя, тип и размер, также можно задать ограничения для данных, которые там будут содержаться. Например:
CREATE TABLE playground (equip_id serial PRIMARY KEY, type varchar (50) NOT NULL, color varchar (25) NOT NULL, location varchar(25) check (location in (‘north’, ‘south’, ‘west’, ‘east’, ‘northeast’, ‘southeast’, ‘southwest’, ‘northwest’)), install_date date );
Мы создали таблицу детской площадки для описания оборудования, которое на ней есть. Сначала идет идентификатор equip_id, который имеет тип serial, это значит, что его значение будет автоматически увеличиваться, ключ primary key значит, что значения должны быть уникальны.
Следующие колонки — обычные строки, для них мы задаем длину поля, они не могут быть пустыми (NOT NULL). Следующий столбец тоже строка, но она может содержать только одно из указанных значений, последний столбец — дата создания.
Вы можете вывести все таблицы, выполнив команду:
Здесь мы видим, что кроме нашей таблицы, существует еще одна переменная -playground_equip_id_seq. В ней содержится последнее значение этого поля. Если нужно вывести только таблицы, выполните:
Выводы
Теперь установка Postgresql в Ubuntu 20.04 завершена, и вы прошли краткий экскурс в синтаксис PgSQL, который очень похож на привычный нам MySQL, но имеет некоторые отличия. Если у вас остались вопросы, спрашивайте в комментариях!