Как узнать имя базы данных postgresql в windows

Using c in PostgreSQL will connect to the named database. How can the name of the current database be determined? Entering: my_db> current_database(); produces: ERRO...

Using c <database_name> in PostgreSQL will connect to the named database.

How can the name of the current database be determined?

Entering:

my_db> current_database();

produces:

ERROR:  syntax error at or near "current_database"
LINE 1: current_database();

Alex Willison's user avatar

asked Feb 5, 2014 at 21:16

Amelio Vazquez-Reina's user avatar

0

The function current_database() returns the name of the current database:

 SELECT current_database();

It’s an SQL function, so you must call it as part of an SQL statement. PostgreSQL doesn’t support running functions as standalone queries, and has no CALL statement like some other SQL engines, so you just use SELECT to call a function.

Erwin Brandstetter's user avatar

answered Feb 6, 2014 at 4:51

Craig Ringer's user avatar

Craig RingerCraig Ringer

53.8k3 gold badges147 silver badges185 bronze badges

1

you can use «conninfo» in psql

answered Oct 8, 2014 at 9:58

John Duddy's user avatar

1

c

prints something like

You are now connected to database "foobar" as user "squanderer".

Use this if you don’t mind creating a new connection, because this is what happens. The connect (shortened as c) without all parameters will create a new connection identical to your current one. The current connection is closed.

See the connect command spec on http://www.postgresql.org/docs/9.3/static/app-psql.html :

If any of dbname, username, host or port are omitted (…)
, the value of that parameter from the previous connection is used.

dezso's user avatar

dezso

29.9k13 gold badges95 silver badges140 bronze badges

answered Mar 3, 2015 at 8:51

shful's user avatar

shfulshful

5014 silver badges2 bronze badges

0

PostgreSQL List Databases — To list all the databases created within PostgreSQL Server.

postgres=# l
postgres=# list

To Check Current database you are connected to.

SELECT current_database();

answered Jul 25, 2020 at 13:13

Santosh Tiwary's user avatar

0

Источник

Using c <database_name> in PostgreSQL will connect to the named database.

How can the name of the current database be determined?

Entering:

my_db> current_database();

produces:

ERROR:  syntax error at or near "current_database"
LINE 1: current_database();

Alex Willison's user avatar

asked Feb 5, 2014 at 21:16

Amelio Vazquez-Reina's user avatar

0

The function current_database() returns the name of the current database:

 SELECT current_database();

It’s an SQL function, so you must call it as part of an SQL statement. PostgreSQL doesn’t support running functions as standalone queries, and has no CALL statement like some other SQL engines, so you just use SELECT to call a function.

Erwin Brandstetter's user avatar

answered Feb 6, 2014 at 4:51

Craig Ringer's user avatar

Craig RingerCraig Ringer

53.8k3 gold badges147 silver badges185 bronze badges

1

you can use «conninfo» in psql

answered Oct 8, 2014 at 9:58

John Duddy's user avatar

1

c

prints something like

You are now connected to database "foobar" as user "squanderer".

Use this if you don’t mind creating a new connection, because this is what happens. The connect (shortened as c) without all parameters will create a new connection identical to your current one. The current connection is closed.

See the connect command spec on http://www.postgresql.org/docs/9.3/static/app-psql.html :

If any of dbname, username, host or port are omitted (…)
, the value of that parameter from the previous connection is used.

dezso's user avatar

dezso

29.9k13 gold badges95 silver badges140 bronze badges

answered Mar 3, 2015 at 8:51

shful's user avatar

shfulshful

5014 silver badges2 bronze badges

0

PostgreSQL List Databases — To list all the databases created within PostgreSQL Server.

postgres=# l
postgres=# list

To Check Current database you are connected to.

SELECT current_database();

answered Jul 25, 2020 at 13:13

Santosh Tiwary's user avatar

0

Источник

Function

Description

format_type ( type oid, typemod integer ) → text

Returns the SQL name for a data type that is identified by its type OID and possibly a type modifier. Pass NULL for the type modifier if no specific modifier is known.

pg_char_to_encoding ( encoding name ) → integer

Converts the supplied encoding name into an integer representing the internal identifier used in some system catalog tables. Returns -1 if an unknown encoding name is provided.

pg_encoding_to_char ( encoding integer ) → name

Converts the integer used as the internal identifier of an encoding in some system catalog tables into a human-readable string. Returns an empty string if an invalid encoding number is provided.

pg_get_catalog_foreign_keys () → setof record ( fktable regclass, fkcols text[], pktable regclass, pkcols text[], is_array boolean, is_opt boolean )

Returns a set of records describing the foreign key relationships that exist within the PostgreSQL system catalogs. The fktable column contains the name of the referencing catalog, and the fkcols column contains the name(s) of the referencing column(s). Similarly, the pktable column contains the name of the referenced catalog, and the pkcols column contains the name(s) of the referenced column(s). If is_array is true, the last referencing column is an array, each of whose elements should match some entry in the referenced catalog. If is_opt is true, the referencing column(s) are allowed to contain zeroes instead of a valid reference.

pg_get_constraintdef ( constraint oid [, pretty boolean ] ) → text

Reconstructs the creating command for a constraint. (This is a decompiled reconstruction, not the original text of the command.)

pg_get_expr ( expr pg_node_tree, relation oid [, pretty boolean ] ) → text

Decompiles the internal form of an expression stored in the system catalogs, such as the default value for a column. If the expression might contain Vars, specify the OID of the relation they refer to as the second parameter; if no Vars are expected, passing zero is sufficient.

pg_get_functiondef ( func oid ) → text

Reconstructs the creating command for a function or procedure. (This is a decompiled reconstruction, not the original text of the command.) The result is a complete CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE statement.

pg_get_function_arguments ( func oid ) → text

Reconstructs the argument list of a function or procedure, in the form it would need to appear in within CREATE FUNCTION (including default values).

pg_get_function_identity_arguments ( func oid ) → text

Reconstructs the argument list necessary to identify a function or procedure, in the form it would need to appear in within commands such as ALTER FUNCTION. This form omits default values.

pg_get_function_result ( func oid ) → text

Reconstructs the RETURNS clause of a function, in the form it would need to appear in within CREATE FUNCTION. Returns NULL for a procedure.

pg_get_indexdef ( index oid [, column integer, pretty boolean ] ) → text

Reconstructs the creating command for an index. (This is a decompiled reconstruction, not the original text of the command.) If column is supplied and is not zero, only the definition of that column is reconstructed.

pg_get_keywords () → setof record ( word text, catcode "char", barelabel boolean, catdesc text, baredesc text )

Returns a set of records describing the SQL keywords recognized by the server. The word column contains the keyword. The catcode column contains a category code: U for an unreserved keyword, C for a keyword that can be a column name, T for a keyword that can be a type or function name, or R for a fully reserved keyword. The barelabel column contains true if the keyword can be used as a “bare” column label in SELECT lists, or false if it can only be used after AS. The catdesc column contains a possibly-localized string describing the keyword’s category. The baredesc column contains a possibly-localized string describing the keyword’s column label status.

pg_get_ruledef ( rule oid [, pretty boolean ] ) → text

Reconstructs the creating command for a rule. (This is a decompiled reconstruction, not the original text of the command.)

pg_get_serial_sequence ( table text, column text ) → text

Returns the name of the sequence associated with a column, or NULL if no sequence is associated with the column. If the column is an identity column, the associated sequence is the sequence internally created for that column. For columns created using one of the serial types (serial, smallserial, bigserial), it is the sequence created for that serial column definition. In the latter case, the association can be modified or removed with ALTER SEQUENCE OWNED BY. (This function probably should have been called pg_get_owned_sequence; its current name reflects the fact that it has historically been used with serial-type columns.) The first parameter is a table name with optional schema, and the second parameter is a column name. Because the first parameter potentially contains both schema and table names, it is parsed per usual SQL rules, meaning it is lower-cased by default. The second parameter, being just a column name, is treated literally and so has its case preserved. The result is suitably formatted for passing to the sequence functions (see Section 9.17).

A typical use is in reading the current value of the sequence for an identity or serial column, for example:

SELECT currval(pg_get_serial_sequence('sometable', 'id'));

pg_get_statisticsobjdef ( statobj oid ) → text

Reconstructs the creating command for an extended statistics object. (This is a decompiled reconstruction, not the original text of the command.)

pg_get_triggerdef ( trigger oid [, pretty boolean ] ) → text

Reconstructs the creating command for a trigger. (This is a decompiled reconstruction, not the original text of the command.)

pg_get_userbyid ( role oid ) → name

Returns a role’s name given its OID.

pg_get_viewdef ( view oid [, pretty boolean ] ) → text

Reconstructs the underlying SELECT command for a view or materialized view. (This is a decompiled reconstruction, not the original text of the command.)

pg_get_viewdef ( view oid, wrap_column integer ) → text

Reconstructs the underlying SELECT command for a view or materialized view. (This is a decompiled reconstruction, not the original text of the command.) In this form of the function, pretty-printing is always enabled, and long lines are wrapped to try to keep them shorter than the specified number of columns.

pg_get_viewdef ( view text [, pretty boolean ] ) → text

Reconstructs the underlying SELECT command for a view or materialized view, working from a textual name for the view rather than its OID. (This is deprecated; use the OID variant instead.)

pg_index_column_has_property ( index regclass, column integer, property text ) → boolean

Tests whether an index column has the named property. Common index column properties are listed in Table 9.72. (Note that extension access methods can define additional property names for their indexes.) NULL is returned if the property name is not known or does not apply to the particular object, or if the OID or column number does not identify a valid object.

pg_index_has_property ( index regclass, property text ) → boolean

Tests whether an index has the named property. Common index properties are listed in Table 9.73. (Note that extension access methods can define additional property names for their indexes.) NULL is returned if the property name is not known or does not apply to the particular object, or if the OID does not identify a valid object.

pg_indexam_has_property ( am oid, property text ) → boolean

Tests whether an index access method has the named property. Access method properties are listed in Table 9.74. NULL is returned if the property name is not known or does not apply to the particular object, or if the OID does not identify a valid object.

pg_options_to_table ( options_array text[] ) → setof record ( option_name text, option_value text )

Returns the set of storage options represented by a value from pg_class.reloptions or pg_attribute.attoptions.

pg_settings_get_flags ( guc text ) → text[]

Returns an array of the flags associated with the given GUC, or NULL if it does not exist. The result is an empty array if the GUC exists but there are no flags to show. Only the most useful flags listed in Table 9.75 are exposed.

pg_tablespace_databases ( tablespace oid ) → setof oid

Returns the set of OIDs of databases that have objects stored in the specified tablespace. If this function returns any rows, the tablespace is not empty and cannot be dropped. To identify the specific objects populating the tablespace, you will need to connect to the database(s) identified by pg_tablespace_databases and query their pg_class catalogs.

pg_tablespace_location ( tablespace oid ) → text

Returns the file system path that this tablespace is located in.

pg_typeof ( "any" ) → regtype

Returns the OID of the data type of the value that is passed to it. This can be helpful for troubleshooting or dynamically constructing SQL queries. The function is declared as returning regtype, which is an OID alias type (see Section 8.19); this means that it is the same as an OID for comparison purposes but displays as a type name.

For example:

SELECT pg_typeof(33);
 pg_typeof
-----------
 integer

SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);
 typlen
--------
      4

COLLATION FOR ( "any" ) → text

Returns the name of the collation of the value that is passed to it. The value is quoted and schema-qualified if necessary. If no collation was derived for the argument expression, then NULL is returned. If the argument is not of a collatable data type, then an error is raised.

For example:

SELECT collation for (description) FROM pg_description LIMIT 1;
 pg_collation_for
------------------
 "default"

SELECT collation for ('foo' COLLATE "de_DE");
 pg_collation_for
------------------
 "de_DE"

to_regclass ( text ) → regclass

Translates a textual relation name to its OID. A similar result is obtained by casting the string to type regclass (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. Also unlike the cast, this does not accept a numeric OID as input.

to_regcollation ( text ) → regcollation

Translates a textual collation name to its OID. A similar result is obtained by casting the string to type regcollation (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. Also unlike the cast, this does not accept a numeric OID as input.

to_regnamespace ( text ) → regnamespace

Translates a textual schema name to its OID. A similar result is obtained by casting the string to type regnamespace (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. Also unlike the cast, this does not accept a numeric OID as input.

to_regoper ( text ) → regoper

Translates a textual operator name to its OID. A similar result is obtained by casting the string to type regoper (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found or is ambiguous. Also unlike the cast, this does not accept a numeric OID as input.

to_regoperator ( text ) → regoperator

Translates a textual operator name (with parameter types) to its OID. A similar result is obtained by casting the string to type regoperator (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. Also unlike the cast, this does not accept a numeric OID as input.

to_regproc ( text ) → regproc

Translates a textual function or procedure name to its OID. A similar result is obtained by casting the string to type regproc (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found or is ambiguous. Also unlike the cast, this does not accept a numeric OID as input.

to_regprocedure ( text ) → regprocedure

Translates a textual function or procedure name (with argument types) to its OID. A similar result is obtained by casting the string to type regprocedure (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. Also unlike the cast, this does not accept a numeric OID as input.

to_regrole ( text ) → regrole

Translates a textual role name to its OID. A similar result is obtained by casting the string to type regrole (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. Also unlike the cast, this does not accept a numeric OID as input.

to_regtype ( text ) → regtype

Translates a textual type name to its OID. A similar result is obtained by casting the string to type regtype (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found. Also unlike the cast, this does not accept a numeric OID as input.
Источник

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

Мы хотим сказать, что нам нужен механизм для проверки, существует ли база данных на нашем сервере PostgreSQL или нет. Более того, бывают ситуации, когда мы хотим, чтобы база данных создавалась после выполнения запроса, если она еще не существует на нашем сервере базы данных. В этом случае в игру вступает нотация «Создать базу данных, если не существует». Это руководство призвано познакомить вас с использованием этой нотации с кратким обсуждением того, поддерживает ли PostgreSQL эту нотацию или нет. После этого мы поделимся с вами альтернативой этой нотации, поддерживаемой PostgreSQL.

Можем ли мы использовать нотацию «Создать базу данных, если не существует» в PostgreSQL в Windows 10?

Обозначение «Создать базу данных, если не существует» поддерживается некоторыми языками программирования. С помощью этой нотации вы можете проверить, существует ли указанная база данных на вашем сервере баз данных или нет, и если она не существует, то эта нотация просто создаст эту базу данных на вашем сервере. Однако поговорим конкретно о PostgreSQL. PostgreSQL не поддерживает эту нотацию, или, другими словами, вы можете сказать, что мы не можем использовать эту нотацию непосредственно в PostgreSQL в Windows 10.

Тем не менее, есть еще несколько способов, которые позволят вам достичь той же функциональности, которую вы можете достичь с помощью этой конкретной нотации. Чтобы узнать больше об этих обходных путях, вам придется продолжить чтение этой статьи.

Если нет, то какое обходное решение мы можем использовать для достижения той же цели?

Поскольку нотация «Создать базу данных, если не существует» не может использоваться в среде PostgreSQL, поэтому мы решили поделиться с вами обходным путем, с помощью которого вы можете достичь той же функциональности. Для этого обходного пути вам нужно будет выполнить несколько иной вариант этой нотации в форме запроса PostgreSQL в Windows 10. Чтобы понять этот вариант, вам нужно будет выполнить шаги, описанные ниже:

Примечание

Не забудьте войти на свой сервер PostgreSQL, прежде чем выполнять эти шаги.

Шаг №1: просмотр существующих баз данных PostgreSQL в Windows 10:

Все мы знаем, что хотим создать конкретную базу данных в PostgreSQL только в том случае, если она уже существует на нашем сервере. База данных, которую мы хотим создать в данном конкретном случае, называется «myNewDB». Поэтому сначала мы попытаемся узнать имена всех наших существующих баз данных PostgreSQL, чтобы узнать, существует ли такая база данных на нашем сервере или нет. Чтобы отобразить имена всех существующих баз данных PostgreSQL, вам необходимо выполнить следующий запрос PostgreSQL в консоли psql:

# SELECT datname FROM pg_database;

Этот запрос извлечет атрибут «datname» из pg_database нашего сервера PostgreSQL. Этот атрибут содержит имена всех существующих баз данных на сервере PostgreSQL. Оператор SELECT в PostgreSQL просто отображает извлеченные имена баз данных на консоли, как показано ниже:

postgres=# SELECT datname FROM pg_database;
datname
postgres sampledb template1 template0 abc abcdb defdb mydb (8 rows)
postgres=#

Из вывода, показанного на изображении выше, видно, что на нашем сервере PostgreSQL нет базы данных с именем «myNewDB»; поэтому мы можем попытаться создать базу данных с этим именем на нашем сервере в Windows 10.

Шаг №2: Создание базы данных PostgreSQL, если она не существует в Windows 10:

Теперь, когда мы увидели, что база данных, которую мы хотим создать, еще не существует на нашем сервере PostgreSQL, поэтому нам нужно будет выполнить следующий запрос для создания этой базы данных:

# SELECT ‘CREATE DATABASE myNewDB’ WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = ‘myNewDB’)gexec

С помощью этого запроса мы создадим базу данных с именем «myNewDB», которой еще не было на нашем сервере PostgreSQL в нашей системе Windows 10. За оператором SELECT в этом запросе следует оператор CREATE DATABASE. После этого мы упомянули имя нашей новой базы данных, которую нужно создать. Вы можете называть его как хотите. Затем мы написали оператор «WHERE NOT EXISTS», который проверяет, существует ли указанная база данных на сервере PostgreSQL или нет. За всеми этими операторами следует подзапрос, в котором есть еще один оператор «SELECT FROM», который проверяет pg_database нашего сервера PostgreSQL, чтобы подтвердить отсутствие базы данных, которую вы пытаетесь создать.

Наконец, есть параметр «gexec», завершающий этот запрос. Этот параметр чрезвычайно важен в этом запросе. Этот параметр отправляет текущий буфер запроса на сервер PostgreSQL, где каждый компонент или атрибут вывода этого запроса обрабатывается как запрос SQL, а не как запрос PostgreSQL. Фактически, это основная причина работы нотации «Создать базу данных, если не существует» в PostgreSQL. В противном случае, даже если вы случайно пропустите этот параметр, вы не сможете реализовать эту функцию в PostgreSQL.

postgres=# SELECT 'CREATE DATABASE myNewDB' WHERE NOT EXISTS (SELECT FROM pg database WHERE datname = 'myNewDB')gexec

Если вы правильно выполните весь синтаксис этого запроса, то база данных PostgreSQL с указанным именем будет успешно создана на сервере PostgreSQL, что вы можете подтвердить из выходного ответа, показанного на изображении ниже:

postgres=# SELECT 'CREATE DATABASE myNewDB' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'myNewDB')gexec
CREATE DATABASE postgres=#

Шаг № 3: Проверка, была ли создана новая база данных PostgreSQL в Windows 10 или нет:

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

# SELECT datname  FROM pg_database;

На данный момент на нашем сервере PostgreSQL существует девять баз данных, и фактически самая последняя из них – это та, которую мы только что попытались создать, как показано ниже:

postgres=# SELECT datname FROM pg_database;
datname
postgres sampledb template1 template0 abc abcdb defdb mydb mynewdb (9 rows)
postgres=#

Заключение:

В этой статье говорилось о нотации «Создать базу данных, если не существует» и ее использовании. Затем мы обсудили, поддерживается ли эта нотация в PostgreSQL или нет. Узнав, что мы не можем использовать эту нотацию непосредственно в PostgreSQL, мы поделились с вами методом достижения той же функциональности, оставаясь в среде PostgreSQL.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Источник
Содержание

Введение
Установка
Проверка версии
Проверка статуса
Перезапуск PostgreSQL
Зайти в Postgres
После установки
l: Список существующих БД
Создание БД
Удалить базу данных
Проверка кодировки
c: Перейти в БД
Подключение к БД
Список активных подключений к БД
Переименовать БД
UPDATE: Изменение значения
Выйти из консоли PostgreSQL
Узнать текущую схему
Системные переменные
Разрешить внешние подключения
Похожие статьи

Введение

PostgreSQL (произносится «Пост-Грэс-Кью-Эл»)
— свободная объектно-реляционная система управления базами данных (СУБД).

Существует в реализациях для множества

UNIX

-подобных платформ, включая AIX,
различные BSD-системы, HP-UX, IRIX,

Linux
, macOS, Solaris/OpenSolaris,
Tru64, QNX, а также для

Microsoft Windows
.

Установка

  • Установка в Ubuntu

  • Установка в Centos

Проверка версии

После установки проверьте версию установленного PostgreSQL

postgres -V

postgres (PostgreSQL) 9.2.24

или

psql -V

psql (PostgreSQL) 13.7

Проверить статус

PostgreSQL 9.6

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 ${PGDATA} (code=exited, status=1/FAILURE)

Feb 07 05:01:31 host.andrei.com systemd[1]: Starting PostgreSQL 11 database server…
Feb 07 05:01:31 host.andrei.com systemd[1]: postgresql-11.service: control process exited, code=exited status=1
Feb 07 05:01:31 host.andrei.com systemd[1]: Failed to start PostgreSQL 11 database server.
Feb 07 05:01:31 host.andrei.com systemd[1]: Unit postgresql-11.service entered failed state.
Feb 07 05:01:31 host.andrei.com systemd[1]: postgresql-11.service failed.

● 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 ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 15904 (postmaster)
Tasks: 8
CGroup: /system.slice/postgresql-11.service
├─15904 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─15906 postgres: logger
├─15908 postgres: checkpointer
├─15909 postgres: background writer
├─15910 postgres: walwriter
├─15911 postgres: autovacuum launcher
├─15912 postgres: stats collector
└─15913 postgres: logical replication launcher

Feb 07 05:01:31 host.andrei.com systemd[1]: Starting PostgreSQL 11 database server…
Feb 07 05:01:31 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.397 EEST [15904] LOG: listening on IPv6 address «::1», port 5432
Feb 07 05:01:31 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.397 EEST [15904] LOG: listening on IPv4 address «127.0.0.1», port 5432
Feb 07 05:01:31 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.401 EEST [15904] LOG: listening on Unix socket «/var/run/postgresql/.s.PGSQL.5432»
Feb 07 05:01:31 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.405 EEST [15904] LOG: listening on Unix socket «/tmp/.s.PGSQL.5432»
Feb 07 05:01:31 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.414 EEST [15904] LOG: redirecting log output to logging collector process
Feb 07 05:01:31 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.414 EEST [15904] HINT: Future log output will appear in directory «log».
Feb 07 05:01:31 host.andrei.com systemd[1]: Started PostgreSQL 11 database server.

Перезапуск PostgreSQL

Для версии 9

systemctl restart postgresql

Для более современных версий, например 11

systemctl restart postgresql-11

Зайти в Postgres

Когда postgresql-11 находится имеет статус active можно
зайти в postgres выполнив

sudo su — postgres

Last login: Tue Feb 07 05:01:31 EEST 2023 on pts/0
-bash-4.2$

psql

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)

В более современных версиях результат будет содеражть номер версии

—————————————-
/var/lib/pgsql/11/data/postgresql.conf
(1 row)

Изучим содержимое

/var/lib/pgsql/data

ll /var/lib/pgsql/data/

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 FilesPostgreSQL12bin) в 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

Получить данные о подключении

conninfo

You are connected to database «postgres» as user «postgres» via socket in «/var/run/postgresql» at port «5432».

Список существующих БД

Список уже существующих на сервере баз данных можно получить командой

l

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’;

CREATE DATABASE

Создадим базу данных heihei с кодировкой utf8 и укажем значения для Collate, Ctype и Template

CREATE DATABASE «heihei» WITH OWNER «postgres» ENCODING
‘UTF8’ LC_COLLATE = ‘C’ LC_CTYPE = ‘C’ TEMPLATE = template0;

CREATE DATABASE

Проверим, что получилось

l

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

сделаю

locale -a

То получу сразу четыре доступные кодировки

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;

CREATE DATABASE

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

Чтобы удалить базу данных воспользуйтесь командой

DROP DATABASE db_name;

DROP DATABASE

Проверить кодировки

Проверить кодировку сервера можно командой

SHOW SERVER_ENCODING;

server_encoding
——————
UTF8
(1 row)

Проверить кодировку клиента

SHOW CLIENT_ENCODING;

client_encoding
——————
WIN1252
(1 row)

Когда вы находитесь в интерактивном режиме PostgreSQL в консоле появляется префикс

db=>

Где db это название текущей базы данных

Показать адрес текущей директории

!

[andrei@localhost ~]$

Вернуться в PostgreSQL можно выполнив

exit

db=>

Зайти в БД

Чтобы начать работу с базой данных нужно знать её имя, например
пусть Вам нужна база даныых, которую назвали просто HeiHei_ru_DB

Воспользуйтесь командой

c 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

Чтобы посмотреть список таблиц введите

dt

List of relations
Schema | Name | Type | Owner
———+———+——-+———-
public | person | table | postgres

(1 row)

Чтобы посмотреть всю таблицу person уже можно использовать стандартный

SELECT * FROM person;

Список активных подключений к БД

Изучить подключения к БД можно выполнив

SELECT * FROM pg_stat_activity WHERE datname = ‘ИМЯ_БД’;

datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_star
t | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type
——-+———+——+———-+———+——————+————-+——————+————-+—————+———-
—+————-+—————+——————+————+——-+————-+—————+——-+—————
(0 rows)

Переименовать БД

ALTER DATABASE старое_имя RENAME TO новое_имя;

Подробности:

Список существующих БД можно получить командой l

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’;

datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_star
t | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type
——-+———+——+———-+———+——————+————-+——————+————-+—————+———-
—+————-+—————+——————+————+——-+————-+—————+——-+—————
(0 rows)

Непосредственно переименование выполняется командой

ALTER DATABASE db01 RENAME TO db02;

ALTER DATABASE

l

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
————+———-+———-+————-+————-+————————
db02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
…
(4 rows)

Если к базе данных кто-то подключен. Например, вы сами из другого терминала

c db02

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-02-07 05:01:31.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 и проверим создалась ли таблица

c heihei

You are now connected to database «heihei» as user «postgres».

heihei=# dt

List of relations
Schema | Name | Type | Owner
———+—————+——-+———-
public | booking_sites | table | postgres
(1 row)

Описание таблицы

Если таблица создана какое-то время назад. Вы могли уже забыть, какие конкретно столбцы она содержит.

Для описания таблицы используется команда d

d booking_sites

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′);

issuer | not_after
———————————————————————————————+————————
CN=192.168.56.174,OU=Andrei CA 3bbce96c-b117-46l0-65a7-f3c04c9f591b | 2027-07-19 16:39:59+03
(1 row)

Обновим значение

aodb=> UPDATE certificates SET not_after=’2022-07-22 16:39:59+03′ WHERE (serial=’2402020070′);

UPDATE 1

Проверим результат

aodb=> SELECT issuer, not_after FROM certificates WHERE (serial=’2402020070263697801′);

issuer | not_after
———————————————————————————————+————————
CN=192.168.56.174,OU=Andrei CA 3bbce96c-b117-46l0-65a7-f3c04c9f591b | 2022-07-22 16:39:59+03
(1 row)

Изменение таблицы

Когда нужно обновить название столбца таблицы используем команду ALTER

Предположим полю website_url не хватает 50 символов. Увеличим длину до 60.

ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR(60);

Проверим изменилась ли таблица

d booking_sites

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)

Выйти из консоли

q

Узнать текущую схему

SELECT current_schema();

current_schema
—————-
public
(1 row)

Переменные окружения

$PGDATA — адрес директории data

echo $PGDATA

/var/lib/pgsql/data

Разрешить внешние подключения

Для PostgreSQL 9

vi /var/lib/pgsql/data/postgresql.conf

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

vi /var/lib/pgsql/11/data/postgresql.conf

# — 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)

vi /var/lib/pgsql/data/pg_hba.conf

Или

vi /var/lib/pgsql/11/data/pg_hba.conf

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

vi /var/lib/pgsql/11/data/postgresql.conf

# — 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’

Похожие статьи

PostgreSQL
Основы
Установка
postgresql.conf: Конфигурационный файл
Таблицы
Пользователи
SELECT
WHERE
bash скрипты для Postgres
Репликация master slave
Write Ahead Log
recovery.conf
pg_hba.conf
Help
Ошибки
Источник

В сети много руководств по PostgreSQL, которые описывают основные команды. Но при погружении глубже в работу возникают такие практические вопросы, для которых требуются продвинутые команды.

Такие команды, или сниппеты, редко описаны в документации. Рассмотрим несколько на примерах, полезных как для разработчиков, так и для администраторов баз данных.

Получение информации о базе данных

Размер базы данных

Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:

SELECT pg_database_size(current_database());

Результат будет представлен как число вида 41809016.

current_database() — функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:

SELECT pg_database_size('my_database');

Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty:

SELECT pg_size_pretty(pg_database_size(current_database()));

В результате получим информацию вида 40 Mb.

Перечень таблиц

Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:

SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','pg_catalog');

information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.

Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:

SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
AND table_schema IN('public', 'myschema');

В последнем условии IN можно указать имя определенной схемы.

Размер таблицы

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

SELECT pg_relation_size('accounts');

Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.

Имя самой большой таблицы

Для того, чтобы вывести список таблиц текущей базы данных, отсортированный по размеру таблицы, выполним следующий запрос:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;

relname — имя таблицы, индекса, представления и т.п.
relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.

Перечень подключенных пользователей

Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;

Активность пользователя

Чтобы узнать активность соединения конкретного пользователя, используем следующий запрос:

SELECT datname FROM pg_stat_activity WHERE usename = 'devuser';

Работа с данными и полями таблиц

Удаление одинаковых строк

Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:

  • дублирующиеся строки,
  • ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).

Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).

Удалить все дубликаты поможет следующий запрос:

DELETE FROM customers WHERE ctid NOT IN
(SELECT max(ctid) FROM customers GROUP BY customers.*);

Уникальное для каждой записи поле ctid по умолчанию скрыто, но оно есть в каждой таблице.

Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.

Теперь рассмотрим случай, когда повторяются значения полей.

Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:

DELETE FROM customers WHERE ctid NOT IN
(SELECT max(ctid) FROM customers GROUP BY customer_id);

Если данные важны, то сначала нужно найти записи с дубликатами:

SELECT * FROM customers WHERE ctid NOT IN
(SELECT max(ctid) FROM customers GROUP BY customer_id);

Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.

Общая форма запроса на удаление описанных выше записей выглядит следующим образом:

DELETE FROM table_name WHERE ctid NOT IN (SELECT max(ctid) FROM table_name GROUP BY column1, [column 2,] );

Безопасное изменение типа поля

Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды ALTER. Давайте для примера снова рассмотрим таблицу с покупателями.

Для поля customer_id используется строковый тип данных varchar. Это ошибка, так как в этом поле предполагается хранить идентификаторы покупателей, которые имеют целочисленный формат integer. Использование varchar неоправданно. Попробуем исправить это недоразумение с помощью команды ALTER:

ALTER TABLE customers ALTER COLUMN customer_id TYPE integer;

Но в результате выполнения получим ошибку:

ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.

Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип varchar, СУБД не может определить принадлежность значения к integer. Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение USING, чтобы корректно преобразовать наши данные в integer:

ALTER TABLE customers ALTER COLUMN customer_id TYPE integer USING (customer_id::integer);

В результате всё прошло без ошибок:

Обратите внимание, что при использовании USING кроме конкретного выражения возможно использование функций, других полей и операторов.

Например, преобразуем поле customer_id обратно в varchar, но с преобразованием формата данных:

ALTER TABLE customers ALTER COLUMN customer_id TYPE varchar USING (customer_id || '-' || first_name);

В результате таблица примет следующий вид:

Поиск «потерянных» значений

Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.

Рассмотрим два варианта поиска.

Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:

SELECT customer_id + 1
FROM customers mo
WHERE NOT EXISTS
(
SELECT NULL
FROM customers mi
WHERE mi.customer_id = mo.customer_id + 1
)
ORDER BY customer_id;

В результате получим значения: 5, 9 и 11.

Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:

WITH seq_max AS (
SELECT max(customer_id) FROM customers
),
seq_min AS (
SELECT min(customer_id) FROM customers
)
SELECT * FROM generate_series((SELECT min FROM seq_min),(SELECT max FROM seq_max))
EXCEPT
SELECT customer_id FROM customers;

В результате видим следующий результат: 5, 9 и 6.

Второй способ
Получаем имя последовательности, связанной с customer_id:

SELECT pg_get_serial_sequence('customers', 'customer_id');

И находим все пропущенные идентификаторы:

WITH sequence_info AS (
SELECT start_value, last_value FROM "SchemaName"."SequenceName"
)
SELECT generate_series ((sequence_info.start_value), (sequence_info.last_value))
FROM sequence_info
EXCEPT
SELECT customer_id FROM customers;

Подсчёт количества строк в таблице

Количество строк вычисляется стандартной функцией count, но её можно использовать с дополнительными условиями.

Общее количество строк в таблице:

SELECT count(*) FROM table;

Количество строк при условии, что указанное поле не содержит NULL:

SELECT count(col_name) FROM table;

Количество уникальных строк по указанному полю:

SELECT count(distinct col_name) FROM table;

Использование транзакций

Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.

Начнём транзакцию с помощью команды BEGIN.

Для того, чтобы откатить все операции, расположенные после BEGIN, используем команду ROLLBACK.

А чтобы применить — команду COMMIT.

Просмотр и завершение исполняемых запросов

Для того, чтобы получить информацию о запросах, выполним следующую команду:

SELECT pid, age(query_start, clock_timestamp()), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):

SELECT pg_cancel_backend(procpid);

Для того, чтобы прекратить работу запроса, выполним:

SELECT pg_terminate_backend(procpid);

Работа с конфигурацией

Поиск и изменение расположения экземпляра кластера

Возможна ситуация, когда на одной операционной системе настроено несколько экземпляров PostgreSQL, которые «сидят» на различных портах. В этом случае поиск пути к физическому размещению каждого экземпляра — достаточно нервная задача. Для того, чтобы получить эту информацию, выполним следующий запрос для любой базы данных интересующего кластера:

SHOW data_directory;

Изменим расположение на другое с помощью команды:

SET data_directory to new_directory_path;

Но для того, чтобы изменения вступили в силу, требуется перезагрузка.

Получение перечня доступных типов данных

Получим перечень доступных типов данных с помощью команды:

SELECT typname, typlen from pg_type where typtype='b';

typname — имя типа данных.
typlen — размер типа данных.

Изменение настроек СУБД без перезагрузки

Настройки PostgreSQL находятся в специальных файлах вроде postgresql.conf и pg_hba.conf. После изменения этих файлов нужно, чтобы СУБД снова получила настройки. Для этого производится перезагрузка сервера баз данных. Понятно, что приходится это делать, но на продакшн-версии проекта, которым пользуются тысячи пользователей, это очень нежелательно. Поэтому в PostgreSQL есть функция, с помощью которой можно применить изменения без перезагрузки сервера:

SELECT pg_reload_conf();

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

Мы рассмотрели команды, которые помогут упростить работу разработчикам и администраторам баз данных, использующим PostgreSQL. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!

По материалам «10 Most Useful PostgreSQL Commands with Examples» и «15 Advanced PostgreSQL Commands with Examples»

Источник

Where are the files for a PostgreSQL database stored?

Asclepius's user avatar

Asclepius

54.4k16 gold badges157 silver badges139 bronze badges

asked Jul 16, 2009 at 11:55

Teifion's user avatar

2

To see where the data directory is, use this query.

show data_directory;

To see all the run-time parameters, use

show all;

You can create tablespaces to store database objects in other parts of the filesystem. To see tablespaces, which might not be in that data directory, use this query.

SELECT *, pg_tablespace_location(oid) FROM pg_tablespace;

SukiCZ's user avatar

SukiCZ

1814 silver badges9 bronze badges

answered Nov 23, 2011 at 4:53

Mike Sherrill 'Cat Recall''s user avatar

6

On Windows7 all the databases are referred by a number in the file named pg_database under C:Program Files (x86)PostgreSQL8.2dataglobal. Then you should search for the folder name by that number under C:Program Files (x86)PostgreSQL8.2database. That is the content of the database.

the Tin Man's user avatar

the Tin Man

157k41 gold badges211 silver badges300 bronze badges

answered Mar 9, 2012 at 7:02

senthilkumari's user avatar

4

Open pgAdmin and go to Properties for specific database. Find OID and then open directory

<POSTGRESQL_DIRECTORY>/data/base/<OID>

There should be your DB files.

answered Oct 27, 2012 at 12:22

Almir Sarajčić's user avatar

Almir SarajčićAlmir Sarajčić

1,4902 gold badges18 silver badges19 bronze badges

1

Under my Linux installation, it’s here: /var/lib/postgresql/8.x/

You can change it with initdb -D "c:/mydb/"

trinth's user avatar

trinth

5,8579 gold badges38 silver badges45 bronze badges

answered Jul 16, 2009 at 11:59

Sadegh's user avatar

SadeghSadegh

6,5544 gold badges33 silver badges44 bronze badges

2

Everyone already answered but just for the latest updates. If you want to know where all the configuration files reside then run this command in the shell

SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

answered Dec 8, 2017 at 3:25

Saad Saadi's user avatar

Saad SaadiSaad Saadi

1,03110 silver badges26 bronze badges

1

I’d bet you’re asking this question because you’ve tried pg_ctl start and received the following error:

pg_ctl: no database directory specified and environment variable PGDATA unset

In other words, you’re looking for the directory to put after -D in your pg_ctl start command.

In this case, the directory you’re looking for contains these files.

PG_VERSION      pg_dynshmem     pg_multixact
pg_snapshots    pg_tblspc       postgresql.conf
base            pg_hba.conf     pg_notify   
pg_stat         pg_twophase     postmaster.opts
global          pg_ident.conf   pg_replslot
pg_stat_tmp     pg_xlog         postmaster.pid
pg_clog         pg_logical      pg_serial
pg_subtrans     postgresql.auto.conf    server.log

You can locate it by locating any of the files and directories above using the search provided with your OS.

For example in my case (a HomeBrew install on Mac OS X), these files are located in /usr/local/var/postgres. To start the server I type:

pg_ctl -D /usr/local/var/postgres -w start

… and it works.

answered Aug 17, 2015 at 16:33

xagg's user avatar

xaggxagg

5105 silver badges11 bronze badges

3

Postgres stores data in files in its data directory. Follow the steps below to go to a database and its files:

The database corresponding to a postgresql table file is a directory. The location of the entire data directory can be obtained by running SHOW data_directory.
in a UNIX like OS (eg: Mac) /Library/PostgreSQL/9.4/data
Go inside the base folder in the data directory which has all the database folders: /Library/PostgreSQL/9.4/data/base

Find the database folder name by running (Gives an integer. This is the database folder name):

SELECT oid from pg_database WHERE datname = <database_name>;

Find the table file name by running (Gives an integer. This is the file name):

SELECT relname, relfilenode FROM pg_class WHERE relname = <table_name>;

This is a binary file. File details such as size and creation date time can be obtained as usual. For more info read this SO thread

Community's user avatar

answered Jun 10, 2015 at 16:04

picmate 涅's user avatar

picmate 涅picmate 涅

3,7835 gold badges42 silver badges50 bronze badges

The location of specific tables/indexes can be adjusted by TABLESPACEs:

CREATE TABLESPACE dbspace LOCATION '/data/dbs';
CREATE TABLE something (......) TABLESPACE dbspace;
CREATE TABLE otherthing (......) TABLESPACE dbspace;

the Tin Man's user avatar

the Tin Man

157k41 gold badges211 silver badges300 bronze badges

answered Jul 16, 2009 at 12:14

ymv's user avatar

ymvymv

2,10313 silver badges20 bronze badges

On Mac: /Library/PostgreSQL/9.0/data/base

The directory can’t be entered, but you can look at the content via: sudo du -hc data

answered Mar 1, 2011 at 3:33

evgeni's user avatar

evgenievgeni

1,6353 gold badges22 silver badges29 bronze badges

6

picmate’s answer is right. on windows the main DB folder location is (at least on my installation)

C:PostgreSQL9.2database

and not in program files.

his 2 scripts, will give you the exact directory/file(s) you need:

SELECT oid from pg_database WHERE datname = <database_name>;
SELECT relname, relfilenode FROM pg_class WHERE relname = <table_name>;

mine is in datname 16393 and relfilenode 41603

database files in postgresql

answered Mar 28, 2019 at 0:25

k3nn's user avatar

k3nnk3nn

1211 silver badge6 bronze badges

1

On Windows, the PGDATA directory that the PostgresSQL docs describe is at somewhere like C:Program FilesPostgreSQL8.1data. The data for a particular database is under (for example) C:Program FilesPostgreSQL8.1database100929, where I guess 100929 is the database number.

answered Sep 14, 2009 at 22:01

Ben Hoyt's user avatar

Ben HoytBen Hoyt

10.4k5 gold badges58 silver badges84 bronze badges

3

I’m running postgres (9.5) in a docker container (on CentOS, as it happens), and as Skippy le Grand Gourou mentions in a comment above, the files are located in /var/lib/postgresql/data/.

$ docker exec -it my-postgres-db-container bash
root@c7d61efe2a5d:/# cd /var/lib/postgresql/data/
root@c7d61efe2a5d:/var/lib/postgresql/data# ls -lh
total 56K
drwx------. 7 postgres postgres   71 Apr  5  2018 base
drwx------. 2 postgres postgres 4.0K Nov  2 02:42 global
drwx------. 2 postgres postgres   18 Dec 27  2017 pg_clog
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_commit_ts
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_dynshmem
-rw-------. 1 postgres postgres 4.4K Dec 27  2017 pg_hba.conf
-rw-------. 1 postgres postgres 1.6K Dec 27  2017 pg_ident.conf
drwx------. 4 postgres postgres   39 Dec 27  2017 pg_logical
drwx------. 4 postgres postgres   36 Dec 27  2017 pg_multixact
drwx------. 2 postgres postgres   18 Nov  2 02:42 pg_notify
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_replslot
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_serial
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_snapshots
drwx------. 2 postgres postgres    6 Sep 16 21:15 pg_stat
drwx------. 2 postgres postgres   63 Nov  8 02:41 pg_stat_tmp
drwx------. 2 postgres postgres   18 Oct 24  2018 pg_subtrans
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_tblspc
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_twophase
-rw-------. 1 postgres postgres    4 Dec 27  2017 PG_VERSION
drwx------. 3 postgres postgres   92 Dec 20  2018 pg_xlog
-rw-------. 1 postgres postgres   88 Dec 27  2017 postgresql.auto.conf
-rw-------. 1 postgres postgres  21K Dec 27  2017 postgresql.conf
-rw-------. 1 postgres postgres   37 Nov  2 02:42 postmaster.opts
-rw-------. 1 postgres postgres   85 Nov  2 02:42 postmaster.pid

answered Nov 8, 2019 at 2:51

rotarydial's user avatar

rotarydialrotarydial

2,0712 gold badges20 silver badges26 bronze badges

A single Terminal command: pg_lsclusters, (using Ubuntu)

What you need is under Data directory:

Ver Cluster Port Status Owner    Data directory               Log file
10  main    5432 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log
11  main    5433 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

answered Aug 18, 2021 at 19:55

Roko's user avatar

RokoRoko

1,21311 silver badges21 bronze badges

Источник

Понравилась статья? Поделить с друзьями:
  • Как узнать дату смены пароля windows
  • Как узнать все ли ядра процессора работают windows 10
  • Как узнать имя администратора в windows 10 через командную строку
  • Как узнать дату последнего обновления windows 10
  • Как узнать все ли драйвера установлены на windows 10