Список продвинутых и не очень команд сервера баз данных PostgreSQL.
Как изменить root пароль в PostgreSQL?
|
1 2 3 4 5 |
$ /usr/local/pgsql/bin/psql postgres postgres Password: (oldpassword) ALTER USER postgres WITH PASSWORD ‘tmppassword’; $ /usr/local/pgsql/bin/psql postgres postgres Password: (tmppassword) |
Изменение пароля для обычного пользователя происходит таким же образом. Пользователь root может поменять пароль любому пользователю
|
1 |
ALTER USER username WITH PASSWORD ‘tmppassword’; |
Как установить PostgreSQL в автозапуск?
|
1 2 3 4 5 |
$ su - root # tar xvfz postgresql-8.3.7.tar.gz # cd postgresql-8.3.7 # cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql # chmod a+x /etc/rc.d/init.d/postgresql |
Проверяем состояние сервера
|
1 2 3 4 5 6 7 |
$ /etc/init.d/postgresql status pg_ctl: server is running (PID: 6171) /usr/local/pgsql/bin/postgres “-D” “/usr/local/pgsql/data” [Замечание: Это сообщение говорит о том, что сервер запущен и работаeт нормально] $ /etc/init.d/postgresql status pg_ctl: no server running [Замечание: Это сообщение говорит о том, что сервер не запущен] |
Как запустить, остановить, перезапустить PostgreSQL?
|
1 2 3 4 5 6 7 8 |
# service postgresql stop Stopping PostgreSQL: server stopped ok # service postgresql start Starting PostgreSQL: ok # service postgresql restart Restarting PostgreSQL: server stopped ok |
Как посмотреть какая версия PostgreSQL запущена?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$ /usr/local/pgsql/bin/psql test Welcome to psql 8.3.7, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=# select version(); version —————————————————————————————————- PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 row) test=# |
Как создать пользователя в PostgreSQL?
Метод 1: Создаем пользователя в через PSQL шелл, командой CREATE USER.
|
1 2 |
CREATE USER ramesh WITH password ‘tmppassword’; CREATE ROLE |
Метод 2: Создаем пользователя в через шелл команду createuser.
|
1 2 3 4 5 |
$ /usr/local/pgsql/bin/createuser testuser Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n CREATE ROLE |
Как создать базу в PostgreSQL?
Метод 1: Создаем базу черезе PSQL шелл, с помощью команды CREATE DATABASE.
|
1 2 |
CREATE DATABASE mydb WITH OWNER postgres; CREATE DATABASE |
Метод 2: Используем команду createdb.
|
1 2 |
$ /usr/local/pgsql/bin/createdb mydb -O postgres CREATE DATABASE |
Получаем список всех баз в PostgreSQL?
|
1 2 3 4 5 6 7 |
# \l List of databases Name | Owner | Encoding ———-+———-+———- postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 |
Как удалить базу в PostgreSQL?
|
1 2 3 4 5 6 7 8 9 10 11 |
# \l List of databases Name | Owner | Encoding ———-+———-+———- backup | postgres | UTF8 mydb | ramesh | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 # DROP DATABASE mydb; DROP DATABASE |
Пользуемя встроенным хелпом к командам
|
1 2 3 |
# \? # \h CREATE # \h CREATE INDEX |
Как получить список всех таблиц в базе данный в PostgreSQL?
|
1 |
# \d |
Для пустой базы вы получите сообщение «No relations found.»
Как узнать время выполнения запроса?
# \timing — после выполения данной команды каждый последующий запрос будет показывать время выполнения.
|
1 2 3 4 |
# \timing Timing is on. # SELECT * from pg_catalog.pg_attribute; Time: 9.583 ms |
Как бэкапить и восстанавливать базы и таблицы в PostgreSQL?
|
1 2 3 |
pg_dump -h localhost -U postgres -F c -f dump.tar.gz pg_restore -h localhost -U postgres -F c -d dump.tar.gz pg_restore -h localhost -U postgres --create --clean -d dump.tar.gz |
Как посмотреть список доступных функций в PostgreSQL?
|
1 2 |
# \df # \df+ |
Как отредактировать запрос к PostgreSQL в редакторе?
|
1 |
# \e |
Где я могу найти файл истории PostgreSQL?
Подобно файлу ~/.bash_history, PostgreSQL хранит все sql команды в файле ~/.psql_history.
|
1 2 3 4 5 6 7 |
$ cat ~/.psql_history alter user postgres with password ‘tmppassword’; \h alter user select version(); create user ramesh with password ‘tmppassword’; \timing select * from pg_catalog.pg_attribute; |
Как сменить базу данных PostgreSQL в шелле SQL?
|
1 |
# \c ; |
Как найти самую большую таблицу в базе данных PostgreSQL?
|
1 2 3 4 5 6 7 8 9 |
# SELECT relname AS "table_name", relpages AS "size_in_pages" FROM pg_class ORDER BY relpages DESC LIMIT 1; |
Результатом будет самая большая таблица (в примере testtable1) в страницах. Размер одной страницы равен 8KB (т.е. размер таблицы в примере — 2,3GB)
|
1 2 3 |
TABLE_NAME | size_in_pages ----------------+--------------- testtable1 | 299211 |
Как узнать размер всей базы данных PostgreSQL?
|
1 2 3 4 5 |
# SELECT pg_database_size( 'sampledb' ); Результатом будет размер базы данных в байтах: pg_database_size ------------------ 27641546936 |
Если вы хотите получить размер в более читаемом («человеческом») формате — «оберните» результат в функцию pg_size_pretty():
|
1 |
# SELECT pg_size_pretty( pg_database_size( 'sampledb' ) ); |
Результат:
|
1 2 3 |
pg_size_pretty ---------------- 26 GB |
Как узнать размер таблицы в базе данных PostgreSQL?
|
1 |
# SELECT pg_size_pretty( pg_total_relation_size( 'testtable1' ) ); |
Результатом будет размер таблицы testtable1, включая индексы. Результат будет отображен сразу в удобном для чтения формате, а не в байтах.
|
1 2 3 |
pg_size_pretty ---------------- 4872 MB |
Если вам нужно узнать размер таблицы без индексов, тогда следует выполнить такой запрос:
|
1 |
# SELECT pg_size_pretty( pg_relation_size( 'testtable1' ) ); |
Результат:
|
1 2 3 |
pg_size_pretty ---------------- 2338 MB |
Как выполнить SQL-файл в PostgreSQL?
Для данной цели существует специальная команда в консольной утилите:
|
1 |
# \i /path/to/file.sql |
Где /path/to/file.sql — это путь к вашему SQL-файлу. Обратите внимание, что он должен лежать в доступной для чтения пользователя postgres директории.
Дать права пользователю PostgreSQL на таблицы в базе данных
|
1 2 3 4 5 6 |
#!/bin/bash for table in `echo "SELECT schemaname || '.' || relname FROM pg_stat_all_tables;" | psql cts | grep -v "pg_" | grep "^ "`; do echo "GRANT SELECT, UPDATE, DELETE, INSERT ON TABLE $table to cts;" echo "GRANT SELECT, UPDATE, DELETE, INSERT ON TABLE $table to cts;" | psql cts done |
Как показать структуру, индексы и прочие элементы выбранной таблицы в PostgreSQL?
Для данной цели существует специальная команда в консольной утилите:
|
1 |
# \d testtable1 |
Где testtable1 — имя таблицы
Результат:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Table "public.testtable1" Column | Type | Modifiers --------------+------------------------+----------- begin_ip | ip4 | not null end_ip | ip4 | not null begin_num | bigint | not null end_num | bigint | not null country_code | character(2) | not null country_name | character varying(255) | not null ip_range | ip4r | Indexes: "testtable1_iprange_index" gist (ip_range) WITH (fillfactor=100) |
Как конвертировать геоданные?
|
1 2 3 4 5 6 |
mgs_pod=# select * from mgs_location_trackpoint where object_id=3622 order by id DESC limit 20; id | time | speed | dir_angle | floor_code | url_get_description | geom | object_id ----------+------------------------+-------+-----------+------------+---------------------+----------------------------------------------------+----------- 35489616 | 2020-04-26 09:46:44+03 | 0 | 0 | | | 0101000020E6100000594C6C3EAECF42407769C36169E04B40 | 3622 35489585 | 2020-04-26 09:46:44+03 | 0 | 0 | | | 0101000020E6100000594C6C3EAECF42407769C36169E04B40 | 3622 35489556 | 2020-04-26 09:46:44+03 | 0 | 0 | | | 0101000020E6100000594C6C3EAECF42407769C36169E04B40 | 3622 |
Превратить их в читабельный вид поможет функция ST_AsText
|
1 2 3 4 5 6 |
mgs_pod=# select id, time, ST_AsText(geom) from mgs_location_trackpoint where object_id=3622 order by id DESC limit 20; id | time | st_astext ----------+------------------------+---------------------------- 35489616 | 2020-04-26 09:46:44+03 | POINT(37.622505 55.753216) 35489585 | 2020-04-26 09:46:44+03 | POINT(37.622505 55.753216) 35489556 | 2020-04-26 09:46:44+03 | POINT(37.622505 55.753216) |