Определение структуры данных
Для создания базы данных используется команда CREATE DATABASE , после которой указывается название базы данных.
Для выполнения запросов будем использовать графический клиент pgAdmin, хотя также можно использовать консольный клиент psql.
Чтобы создать новую базу, данных откроем pgAdmin . В левой части программы выберем какую-нибудь базу данных, например, стандартную бд postgres, и нажмем на нее правой кнопкой мыши.
В появившемся меню выберем пункт Query Tool. , и в центральной части программы откроется поле для ввода кода SQL. В это поле введем следующий код:
Для выполнения кода нажмем на значок молнии, и после этого будет создана база данных usersdb.
Чтобы увидеть нашу базу данных, нажмем в левой части на узел Databases правой кнопкой мыши и в контекстном меню выберем Refresh. :
Произойдет обновление, и мы увидем созданную базу данных.
По умолчанию база является неактивной, поэтому ее значок имеет серый цвет. Но чтобы к ней подключиться, достаточно нажать на нее и раскрыть ее узел.
Удаление базы данных
Для удаления базы данных применяется команда DROP DATABASE , после которой указывается название базы данных.
Удаляемая база данных должна быть неактивной, то есть подключение к ней должно быть закрыта.
Creating user, database and adding access on PostgreSQL
One nice thing about PGSQL is it comes with some utility binaries like createuser and createdb. So we will be making use of that.
As the default configuration of Postgres is, a user called postgres is made on and the user postgres has full superadmin access to entire PostgreSQL instance running on your OS.
The above command gets you the psql command line interface in full admin mode.
In the following commands, keep in mind the < angular brackets > are to denote variables you have to set yourself. In the actual command, omit the <>
Creating user
Creating Database
Giving the user a password
Granting privileges on database
And yeah, that should be pretty much it !
Doing purely via psql
Your OS might not have the createuser or createdb binaries, or you may, for some reason want to do it purely via psql, then these are the three magic commands —
Obligatory shameless self-plug :
I am one of the co-founders of Coding Blocks — A Software Programming bootcamp, based out of New Delhi, India. Among other things, we teach Full Stack Web Development using NodeJS, via both classroom programmes, as well as online classes. You can follow our Medium to find more articles on Android and Web development.
PostgreSQL
PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (СУБД).
Существует в реализациях для множества UNIX -подобных платформ, включая AIX, различные BSD-системы, HP-UX, IRIX, Linux , macOS, Solaris/OpenSolaris, Tru64, QNX, а также для Microsoft Windows .
Установка
- Установка в Ubuntu
- Установка в Centos
Проверка версии
После установки проверьте версию установленного PostgreSQL
postgres (PostgreSQL) 9.2.24
psql (PostgreSQL) 13.7
Проверить статус
systemctl status -l postgresql
Вместо 11 укажите вашу версию
systemctl status -l postgresql- 11
Возможны варианты inactive, failed, active
● postgresql-11.service — PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: inactive (dead) Docs: https://www.postgresql.org/docs/11/static/
● postgresql-11.service — PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: failed (Result: exit-code) since Tue 2022-09-20 12:01:41 EEST; 6s ago Docs: https://www.postgresql.org/docs/11/static/ Process: 12952 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir $
● postgresql-11.service — PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: active (running) since Tue 2022-09-20 12:19:45 EEST; 7s ago Docs: https://www.postgresql.org/docs/11/static/ Process: 15899 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir $
Перезапуск PostgreSQL
systemctl restart postgresql
Для более современных версий, например 11
systemctl restart postgresql-11
Зайти в Postgres
Когда postgresql-11 находится имеет статус active можно зайти в postgres выполнив
sudo su — postgres
Last login: Fri Mar 03 23:55:47 EEST 2023 on pts/0 -bash-4.2$
psql (11.17) Type "help" for help. postgres=#
После установки
Расположение файлов с настройками, например, postgresql.conf можно получить выполнив
-bash-4.2$ su — postgres -c «psql -c 'SHOW config_file;'»
В PostgreSQL 9 результат будет таким:
Password: config_file ————————————- /var/lib/pgsql/data/postgresql.conf (1 row)
В более современных версиях результат будет содеражть номер версии
total 48 drwx——. 7 postgres postgres 67 Jun 9 22:54 base drwx——. 2 postgres postgres 4096 Jun 9 23:19 global drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_clog -rw——-. 1 postgres postgres 4371 Jun 10 01:23 pg_hba.conf -rw——-. 1 postgres postgres 1636 Jun 9 13:54 pg_ident.conf drwx——. 2 postgres postgres 58 Jun 10 00:00 pg_log drwx——. 4 postgres postgres 36 Jun 9 13:54 pg_multixact drwx——. 2 postgres postgres 18 Jun 9 14:14 pg_notify drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_serial drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_snapshots drwx——. 2 postgres postgres 25 Jun 10 02:06 pg_stat_tmp drwx——. 2 postgres postgres 18 Jun 9 13:54 pg_subtrans drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_tblspc drwx——. 2 postgres postgres 6 Jun 9 13:54 pg_twophase -rw——-. 1 postgres postgres 4 Jun 9 13:54 PG_VERSION drwx——. 3 postgres postgres 60 Jun 9 13:54 pg_xlog -rw——-. 1 postgres postgres 19889 Jun 10 01:43 postgresql.conf -rw——-. 1 postgres postgres 45 Jun 9 14:14 postmaster.opts -rw——-. 1 postgres postgres 92 Jun 9 14:14 postmaster.pid
Подключение к БД
Чтобы получить доступ к базам данных можно воспользоваться стандартной консолью psql.
Windows:
Если Вы хотите подключиться из bash в Windows не забудьте добавить местоположение psql.exe (у меня это C:\Program Files\PostgreSQL\12\bin) в PATH
Как это делается описано в статье PATH
psql.exe -h localhost -p 5433 -U postgres
Linux:
sudo su — postgres
psql
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1)) Type «help» for help.
Если БД на локальном хосте
psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME
Получить данные о подключении
You are connected to database «postgres» as user «postgres» via socket in «/var/run/postgresql» at port «5432».
Список существующих БД
Список уже существующих на сервере баз данных можно получить командой
Name | Owner | Encoding | Collate | Ctype | Access privileges —————+———-+———-+—————————-+—————————-+———————— urn.su | postgres | UTF8 | English_United States.1252 | English_United States.1252 | topbicyle.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (6 rows)
Обратите внимание на столбцы Encoding, Collate и Ctype. Знание кодировок может Вам пригодиться в будущем.
Создать базу данных
Создадим базу данных heihei_ru_db с кодировкой utf8
CREATE DATABASE «heihei_ru_db» WITH OWNER «postgres» ENCODING 'UTF8';
Создадим базу данных heihei с кодировкой utf8 и укажем значения для Collate, Ctype и Template
CREATE DATABASE «heihei» WITH OWNER «postgres» ENCODING
'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0;
Проверим, что получилось
Name | Owner | Encoding | Collate | Ctype | Access privileges —————+———-+———-+—————————-+—————————-+———————— heihei | postgres | UTF8 | C | C | urn.su | postgres | UTF8 | English_United States.1252 | English_United States.1252 | topbicyle.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (7 rows)
Если я в bash сделаю
То получу сразу четыре доступные кодировки
C
C.UTF-8
en_US.utf8
POSIX
Но создать БД, например, с en_US.utf8 у меня не получается
CREATE DATABASE «heihei» WITH OWNER «postgres» ENCODING 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8' TEMPLATE = template0;
Приводит к ошибке
ERROR: invalid locale name: «en_US.UTF-8»
Если Вы знаете как решить эту проблему — отпишитесь в комментариях к статье.
UPD: Выполнил такую же команду в Ubuntu и база успешно создана
CREATE DATABASE «new_db» WITH OWNER «postgres» ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE = template0;
Удалить базу данных
Чтобы удалить базу данных воспользуйтесь командой
DROP DATABASE db_name;
Проверить кодировки
Проверить кодировку сервера можно командой
server_encoding —————— UTF8 (1 row)
Проверить кодировку клиента
client_encoding —————— WIN1252 (1 row)
Когда вы находитесь в интерактивном режиме PostgreSQL в консоле появляется префикс
Где db это название текущей базы данных
Показать адрес текущей директории
Вернуться в PostgreSQL можно выполнив
Зайти в БД
Чтобы начать работу с базой данных нужно знать её имя, например пусть Вам нужна база даныых, которую назвали просто HeiHei_ru_DB
You are now connected to database «HeiHei_ru_DB» as user «postgres».
Если вы работаете в Linux и база данных находится на том же хосте можно выполнить
psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME
Чтобы посмотреть список таблиц введите
List of relations
Schema | Name | Type | Owner
———+———+——-+———-
public | person | table | postgres
(1 row)
Чтобы посмотреть всю таблицу person уже можно использовать стандартный
SELECT * FROM person;
Список активных подключений к БД
Изучить подключения к БД можно выполнив
SELECT * FROM pg_stat_activity WHERE datname = 'ИМЯ_БД';
Переименовать БД
ALTER DATABASE старое_имя RENAME TO новое_имя;
Список существующих БД можно получить командой \l
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ————+———-+———-+————-+————-+———————— db01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)
Если нужно переименовать БД db01 первым делом нужно выйти из неё.
Для этого можно зайти в другую БД, например, postgres.
Изучить подключения к БД можно выполнив
SELECT * FROM pg_stat_activity WHERE datname = 'db01';
Непосредственно переименование выполняется командой
ALTER DATABASE db01 RENAME TO db02;
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ————+———-+———-+————-+————-+———————— db02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | … (4 rows)
Если к базе данных кто-то подключен. Например, вы сами из другого терминала
You are now connected to database "db02" as user "postgres".
postgres=# SELECT datname, pid, backend_start, state FROM pg_stat_activity WHERE datname = 'db02';
datname | pid | backend_start | state ———+——-+——————————-+——- db02 | 21238 | 2023-03-03 23:55:47.503073+03 | idle (1 row)
Все подключения можно закрыть выполнив
postgres=# SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = 'db02';
pg_terminate_backend ———————- t (1 row)
Достать значение из БД
Пример — запишем значение из базы данных в файл с помощью echo
echo $ ( psql -h localhost -d db_name -U db_user -c " SELECT host_certificate FROM hosts WHERE ( addresses= '192.168.56.200' ) ; " ) > raw_cert
Содержание базы данных
В предыдущем параграфе мы создали в базе данных heihei таблицу booking_sites
Убедимся в том, что скрипт отработал удачно
Перейдём в базу данных heihei и проверим создалась ли таблица
You are now connected to database «heihei» as user «postgres».
List of relations Schema | Name | Type | Owner ———+—————+——-+———- public | booking_sites | table | postgres (1 row)
Описание таблицы
Если таблица создана какое-то время назад. Вы могли уже забыть, какие конкретно столбцы она содержит.
Для описания таблицы используется команда \d
Table «public.booking_sites» Column | Type | Collation | Nullable | Default —————-+————————+————+———-+——————————————- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(50) | | | Indexes: «booking_sites_pkey» PRIMARY KEY, btree (id)
UPDATE: Изменение значения
Изменим дату годности сертификата в таблице certificates
Изучим текущую ситуацию
aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070');
aodb=> UPDATE certificates SET not_after='2022-07-22 16:39:59+03' WHERE (serial='2402020070');
aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070263697801');
Изменение таблицы
Когда нужно обновить название столбца таблицы используем команду ALTER
Предположим полю website_url не хватает 50 символов. Увеличим длину до 60.
ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR(60);
Проверим изменилась ли таблица
Table «public.booking_sites» Column | Type | Collation | Nullable | Default —————-+————————+————+———-+——————————————- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(60) | | | Indexes: «booking_sites_pkey» PRIMARY KEY, btree (id)
Выйти из консоли
Узнать текущую схему
current_schema —————- public (1 row)
Переменные окружения
$PGDATA — адрес директории data
Разрешить внешние подключения
Для PostgreSQL 9
Для более современных версий PostgreSQL нужно указать директорию, например 11
# — Connection Settings — # listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) listen_addresses = '*' port = 5432 # (change requires restart)
host all all 0.0.0.0/0 md5
Включить SSL
openssl genrsa -des3 -out server.key 1024
openssl rsa -in server.key -out server.key
chmod 400 server.key
chown postgres.postgres server.key
openssl req -new -key server.key -days 3650 -out server.crt -x509
cp server.crt root.crt
# — SSL — ssl = on #ssl_ca_file = '' ssl_cert_file = '/var/lib/pgsql/11/data/server.crt' #ssl_crl_file = '' ssl_key_file = '/var/lib/pgsql/11/data/server.key'
Подпишитесь на Telegram канал @aofeed чтобы следить за выходом новых статей и обновлением старых
Как создать базу данных в postgresql
CREATE DATABASE creates a new PostgreSQL database.
To create a database, you must be a superuser or have the special CREATEDB privilege. See CREATE ROLE .
By default, the new database will be created by cloning the standard system database template1 . A different template can be specified by writing TEMPLATE name . In particular, by writing TEMPLATE template0 , you can create a pristine database (one where no user-defined objects exist and where the system objects have not been altered) containing only the standard objects predefined by your version of PostgreSQL . This is useful if you wish to avoid copying any installation-local objects that might have been added to template1 .
Parameters
The name of a database to create.
The role name of the user who will own the new database, or DEFAULT to use the default (namely, the user executing the command). To create a database owned by another role, you must be a direct or indirect member of that role, or be a superuser.
The name of the template from which to create the new database, or DEFAULT to use the default template ( template1 ).
Character set encoding to use in the new database. Specify a string constant (e.g., ‘SQL_ASCII’ ), or an integer encoding number, or DEFAULT to use the default encoding (namely, the encoding of the template database). The character sets supported by the PostgreSQL server are described in Section 24.3.1. See below for additional restrictions.
Strategy to be used in creating the new database. If the WAL_LOG strategy is used, the database will be copied block by block and each block will be separately written to the write-ahead log. This is the most efficient strategy in cases where the template database is small, and therefore it is the default. The older FILE_COPY strategy is also available. This strategy writes a small record to the write-ahead log for each tablespace used by the target database. Each such record represents copying an entire directory to a new location at the filesystem level. While this does reduce the write-ahead log volume substantially, especially if the template database is large, it also forces the system to perform a checkpoint both before and after the creation of the new database. In some situations, this may have a noticeable negative impact on overall system performance.
This is a shortcut for setting LC_COLLATE and LC_CTYPE at once.
The other locale settings lc_messages, lc_monetary, lc_numeric, and lc_time are not fixed per database and are not set by this command. If you want to make them the default for a specific database, you can use ALTER DATABASE . SET .
Collation order ( LC_COLLATE ) to use in the new database. This affects the sort order applied to strings, e.g., in queries with ORDER BY, as well as the order used in indexes on text columns. The default is to use the collation order of the template database. See below for additional restrictions.
Character classification ( LC_CTYPE ) to use in the new database. This affects the categorization of characters, e.g., lower, upper and digit. The default is to use the character classification of the template database. See below for additional restrictions.
Specifies the ICU locale ID if the ICU locale provider is used.
Specifies the provider to use for the default collation in this database. Possible values are: icu , libc . libc is the default. The available choices depend on the operating system and build options.
Specifies the collation version string to store with the database. Normally, this should be omitted, which will cause the version to be computed from the actual version of the database collation as provided by the operating system. This option is intended to be used by pg_upgrade for copying the version from an existing installation.
See also ALTER DATABASE for how to handle database collation version mismatches.
The name of the tablespace that will be associated with the new database, or DEFAULT to use the template database’s tablespace. This tablespace will be the default tablespace used for objects created in this database. See CREATE TABLESPACE for more information.
If false then no one can connect to this database. The default is true, allowing connections (except as restricted by other mechanisms, such as GRANT / REVOKE CONNECT ).
How many concurrent connections can be made to this database. -1 (the default) means no limit.
If true, then this database can be cloned by any user with CREATEDB privileges; if false (the default), then only superusers or the owner of the database can clone it.
The object identifier to be used for the new database. If this parameter is not specified, PostgreSQL will choose a suitable OID automatically. This parameter is primarily intended for internal use by pg_upgrade , and only pg_upgrade can specify a value less than 16384.
Optional parameters can be written in any order, not only the order illustrated above.
Notes
CREATE DATABASE cannot be executed inside a transaction block.
Errors along the line of “ could not initialize database directory ” are most likely related to insufficient permissions on the data directory, a full disk, or other file system problems.
Use DROP DATABASE to remove a database.
The program createdb is a wrapper program around this command, provided for convenience.
Database-level configuration parameters (set via ALTER DATABASE ) and database-level permissions (set via GRANT ) are not copied from the template database.
Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose “ COPY DATABASE ” facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes. See Section 23.3 for more information.
The character set encoding specified for the new database must be compatible with the chosen locale settings ( LC_COLLATE and LC_CTYPE ). If the locale is C (or equivalently POSIX ), then all encodings are allowed, but for other locale settings there is only one encoding that will work properly. (On Windows, however, UTF-8 encoding can be used with any locale.) CREATE DATABASE will allow superusers to specify SQL_ASCII encoding regardless of the locale settings, but this choice is deprecated and may result in misbehavior of character-string functions if data that is not encoding-compatible with the locale is stored in the database.
The encoding and locale settings must match those of the template database, except when template0 is used as template. This is because other databases might contain data that does not match the specified encoding, or might contain indexes whose sort ordering is affected by LC_COLLATE and LC_CTYPE . Copying such data would result in a database that is corrupt according to the new settings. template0 , however, is known to not contain any data or indexes that would be affected.
There is currently no option to use a database locale with nondeterministic comparisons (see CREATE COLLATION for an explanation). If this is needed, then per-column collations would need to be used.
The CONNECTION LIMIT option is only enforced approximately; if two new sessions start at about the same time when just one connection “ slot ” remains for the database, it is possible that both will fail. Also, the limit is not enforced against superusers or background worker processes.
Examples
To create a new database:
To create a database sales owned by user salesapp with a default tablespace of salesspace :
To create a database music with a different locale:
In this example, the TEMPLATE template0 clause is required if the specified locale is different from the one in template1 . (If it is not, then specifying the locale explicitly is redundant.)
To create a database music2 with a different locale and a different character set encoding:
The specified locale and encoding settings must match, or an error will be reported.
Note that locale names are specific to the operating system, so that the above commands might not work in the same way everywhere.
Compatibility
There is no CREATE DATABASE statement in the SQL standard. Databases are equivalent to catalogs, whose creation is implementation-defined.
See Also
| Prev | Up | Next |
| CREATE CONVERSION | Home | CREATE DOMAIN |
Submit correction
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.