Документация – тут>>> и тут>>>.
Доступ к AWS PostgreSQL RDS из нашей сети закрыт, надо установить PostgreSQL локально для проверки конвертации баз MySQL > PostgreSQL.
Быстрая заметка по началу работы с сервером.
Содержание
Установка
Устанавливаем на Arch Linux, для тестов:
[simterm]
$ sudo pacman -S postgresql
[/simterm]
Задаём пароль пользователю postgres:
[simterm]
$ sudo passwd postgres New password: Retype new password: passwd: password updated successfully
[/simterm]
Обновляем sudo через visudo:
... postgres ALL=(ALL) NOPASSWD:ALL ...
Переключаемся под него:
[simterm]
16:58:43 [setevoy@setevoy-arch-work ~] $ su -l postgres Password: [postgres@setevoy-arch-work ~]$
[/simterm]
Базы данных будут хранится в /var/lib/postgres/data:
[simterm]
[postgres@setevoy-arch-work ~]$ ls -l /var/lib/postgres/data/ total 0
[/simterm]
Создаём database cluster – набор дефолтных баз данных:
[simterm]
[postgres@setevoy-arch-work ~]$ initdb -D /var/lib/postgres/data/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgres/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /var/lib/postgres/data/ -l logfile start
[/simterm]
Проверяем:
[simterm]
[postgres@setevoy-arch-work ~]$ ls -l /var/lib/postgres/data/ total 112 drwx------ 5 postgres postgres 4096 Oct 20 17:03 base drwx------ 2 postgres postgres 4096 Oct 20 17:03 global drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_clog drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_commit_ts drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_dynshmem -rw------- 1 postgres postgres 4468 Oct 20 17:03 pg_hba.conf -rw------- 1 postgres postgres 1636 Oct 20 17:03 pg_ident.conf drwx------ 4 postgres postgres 4096 Oct 20 17:03 pg_logical drwx------ 4 postgres postgres 4096 Oct 20 17:03 pg_multixact drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_notify drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_replslot drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_serial drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_snapshots drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_stat drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_stat_tmp drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_subtrans drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_tblspc drwx------ 2 postgres postgres 4096 Oct 20 17:03 pg_twophase -rw------- 1 postgres postgres 4 Oct 20 17:03 PG_VERSION drwx------ 3 postgres postgres 4096 Oct 20 17:03 pg_xlog -rw------- 1 postgres postgres 88 Oct 20 17:03 postgresql.auto.conf -rw------- 1 postgres postgres 22328 Oct 20 17:03 postgresql.conf
[/simterm]
Запускаем сервер – либо с помощью pg_ctl:
[simterm]
[postgres@setevoy-arch-work ~]$ sudo mkdir /run/postgresql/ [postgres@setevoy-arch-work ~]$ sudo chown postgres:postgres /run/postgresql/ [postgres@setevoy-arch-work ~]$ pg_ctl start -D /var/lib/postgres/data/ server starting [postgres@setevoy-arch-work ~]$ LOG: database system was shut down at 2017-10-20 17:03:22 EEST LOG: MultiXact member wraparound protections are now enabled LOG: autovacuum launcher started LOG: database system is ready to accept connections
[/simterm]
Либо – через systemctl:
[simterm]
$ systemctl start postgresql.service ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ==== Authentication is required to start 'postgresql.service'. Authenticating as: setevoy Password: ==== AUTHENTICATION COMPLETE ====
[/simterm]
Проверяем порт и bind IP:
[simterm]
17:14:42 [setevoy@setevoy-arch-work ~] $ sudo netstat -anp | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 14466/postgres tcp6 0 0 ::1:5432 :::* LISTEN 14466/postgres unix 2 [ ACC ] STREAM LISTENING 10959528 14466/postgres /run/postgresql/.s.PGSQL.5432
[/simterm]
Пользователь
Создаём нового пользователя с помощью createuser:
[simterm]
[postgres@setevoy-arch-work ~]$ createuser --interactive Enter name of role to add: testuser1 Shall the new role be a superuser? (y/n) y
[/simterm]
Подключаемся под “рутом” (postrgres):
[simterm]
[postgres@setevoy-arch-work ~]$ psql psql (9.6.5) Type "help" for help. postgres=#
[/simterm]
Проверяем пользователей:
[simterm]
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
testuser1 | Superuser, Create role, Create DB | {}
[/simterm]
Выходим, подключаемся (Ctrl+D) под пользователем testuser1 к базе postgres:
[simterm]
[postgres@setevoy-arch-work ~]$ psql --username testuser1 postgres psql (9.6.5) Type "help" for help. postgres=#
[/simterm]
Создание базы
ОК, выходим, создаём базу с помощью createdb:
[simterm]
[postgres@setevoy-arch-work ~]$ createdb testdb2
[/simterm]
Проверяем:
[simterm]
[postgres@setevoy-arch-work ~]$ psql -c '\l'
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
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
testdb2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
[/simterm]
Или из консоли Postgres:
[simterm]
postgres=# CREATE DATABASE testdb3; CREATE DATABASE
[/simterm]
Проверяем – “+” для деталей:
[simterm]
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+-----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7063 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 6953 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 6953 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
testdb2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 6953 kB | pg_default |
testdb3 | testuser1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 6953 kB | pg_default |
[/simterm]
Отобразить только базы test*:
[simterm]
postgres=# \l+ test*
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
---------+-----------+----------+-------------+-------------+-------------------+---------+------------+-------------
testdb2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 6953 kB | pg_default |
testdb3 | testuser1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 6953 kB | pg_default |
[/simterm]
Переключиться на базу:
[simterm]
postgres=# \c testdb3 You are now connected to database "testdb3" as user "testuser1".
[/simterm]
Создание таблиц
Просмотреть все таблицы (пока нет ни одной):
[simterm]
testdb3=# \dt No relations found.
[/simterm]
Создать таблицу:
[simterm]
testdb3=# CREATE TABLE testtable (ID INT PRIMARY KEY NOT NULL, Name TEXT NOT NULL);
[/simterm]
Проверяем ещё раз:
[simterm]
testdb3=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+-----------
public | testtable | table | testuser1
[/simterm]
И помощь – \?:
[simterm]
postgres=# \? General \copyright show PostgreSQL usage and distribution terms \errverbose show most recent error message at maximum verbosity \g [FILE] or ; execute query (and send results to file or |pipe) \gexec execute query, then execute each value in its result \gset [PREFIX] execute query and store results in psql variables \q quit psql \crosstabview [COLUMNS] execute query and display results in crosstab \watch [SEC] execute query every SEC seconds Help \? [commands] show help on backslash commands \? options show help on psql command-line options ...
[/simterm]
Готово.
