Смотрим список подключений:
SELECT pid, usename, client_addr, backend_start, query FROM
pg_stat_activity WHERE datname = 'dbname';Сбрасываем все подключения к бд (кроме того из под которого работаем сейчас, если мы подключены к базе dbname):
SELECT pg_terminate_backend( pid ) FROM pg_stat_activity WHERE pid <>
pg_backend_pid( ) AND datname = 'dbname';
В данной инструкции рассмотрены варианты и примеры создания резервных копий и восстановления баз СУБД PostgreSQL.
Все команды, которые приводятся ниже, должны выполняться из командной строки. В Linux — это окно терминала, в Windows — командная строка (cmd.exe) с переходом в папку установки PostgreSQL. Читать
Выпущена новая версия PostgreSQL 14 . Есть несколько способов обновить старую версию 13, и самый простой из них — использовать инструмент pg_upgrade . Вот краткое руководство для систем Ubuntu (или Debian). И, пожалуйста, не забудьте сделать резервную копию ваших данных!
Обновите пакеты и установите новый PostgreSQL 14.
sudo apt-get update
sudo apt-get install postgresql-14 postgresql-server-dev-14
Проверьте, нет ли различий в файлах конфигурации.
diff /etc/postgresql/13/main/postgresql.conf /etc/postgresql/14/main/postgresql.conf
diff /etc/postgresql/13/main/pg_hba.conf /etc/postgresql/14/main/pg_hba.conf
Остановите службу PostgreSQL.
sudo systemctl stop postgresql.service
Войдите как postgresпользователь.
sudo su - postgres
Проверьте кластеры (обратите внимание на --checkаргумент, это не изменит никаких данных).
/usr/lib/postgresql/14/bin/pg_upgrade
--old-datadir=/var/lib/postgresql/13/main
--new-datadir=/var/lib/postgresql/14/main
--old-bindir=/usr/lib/postgresql/13/bin
--new-bindir=/usr/lib/postgresql/14/bin
--old-options '-c config_file=/etc/postgresql/13/main/postgresql.conf'
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'
--check
Перенесите данные (без --checkаргумента).
/usr/lib/postgresql/14/bin/pg_upgrade
--old-datadir=/var/lib/postgresql/13/main
--new-datadir=/var/lib/postgresql/14/main
--old-bindir=/usr/lib/postgresql/13/bin
--new-bindir=/usr/lib/postgresql/14/bin
--old-options '-c config_file=/etc/postgresql/13/main/postgresql.conf'
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'
Вернитесь к обычному пользователю.
exit
Поменяйте местами порты для старой и новой версий PostgreSQL.
sudo vim /etc/postgresql/14/main/postgresql.conf
# ...and change "port = 5433" to "port = 5432"
sudo vim /etc/postgresql/13/main/postgresql.conf
# ...and change "port = 5432" to "port = 5433"
Запустите службу PostgreSQL.
sudo systemctl start postgresql.service
Войдите postgresснова как пользователь.
sudo su - postgres
Проверьте новую версию PostgreSQL.
psql -c "SELECT version();"
Запустите рекомендуемую vacuumdbкоманду:
/usr/lib/postgresql/14/bin/vacuumdb --all --analyze-in-stages
В настройках по умолчанию в PostgreSQL 14 есть одно важное изменение. Собственно, вы могли заметить его, проверяя различия в конфигурационных файлах: шифрование паролей. Раньше это был MD5, теперь SHA256. Таким образом, если вы использовали конфигурацию по умолчанию, ваши клиенты не смогут подключиться к новой базе данных. А так как PostgreSQL не знает исходных паролей в открытом виде, вам придется установить их снова для всех пользователей вашей базы данных.
Для этого подключитесь к новой базе данных:
psql
Вы можете перечислить всех существующих пользователей базы данных:
du
Чтобы изменить (или установить заново) пароль пользователя, вы можете использовать следующую команду (повторить для каждого пользователя):
password user_name
Теперь вы можете выйти из psql.
q
И вернуться к обычному пользователю.
exit
Проверьте, какие старые пакеты PostgreSQL установлены.
apt list --installed | grep postgresql
Удалите старые пакеты PostgreSQL (из списка выше).
sudo apt-get remove postgresql-13 postgresql-server-dev-13
Удалите старую конфигурацию.
sudo rm -rf /etc/postgresql/13/
Войдите как postgresпользователь еще раз.
sudo su - postgres
Наконец, удалите старые данные кластера.
./delete_old_cluster.sh
Готово!
PS: Если вы хотите перейти на Postgres 13 или более раннюю версию, ознакомьтесь с более ранним руководством , в этом процессе есть некоторые важные изменения.
Источник: https://www.kostolansky.sk/posts/upgrading-to-postgresql-14/
PostgreSQL 12 вышел 03.10.2019. Обновится со старой версии можно через pg_dumpall и pg_upgrade. Ниже описан вариант обновления через pg_upgrade.
Установите PostgreSQL 12:
sudo apt-get update
sudo apt-get install postgresql-12 postgresql-server-dev-12
Перенесите ваши кастомные настройки из старых конфигов в новые. Различия конфигов разных версий удобно посмотреть командами:
diff /etc/postgresql/11/main/postgresql.conf /etc/postgresql/12/main/postgresql.conf
diff /etc/postgresql/11/main/pg_hba.conf /etc/postgresql/12/main/pg_hba.conf
Остановите запущенный PostgreSQL:
sudo systemctl stop postgresql.service
Перейдите в каталог с временными файлами. Туда будут записаны логи и добавлены некоторые скрипты:
cd /tmp
Запускаем работу в командной строке от пользователя postgres:
sudo su postgres
Безопасно проверяем кластеры, без изменения каких-либо данных:
/usr/lib/postgresql/12/bin/pg_upgrade
— old-datadir=/var/lib/postgresql/11/main
— new-datadir=/var/lib/postgresql/12/main
— old-bindir=/usr/lib/postgresql/11/bin
— new-bindir=/usr/lib/postgresql/12/bin
— old-options ‘-c config_file=/etc/postgresql/11/main/postgresql.conf’
— new-options ‘-c config_file=/etc/postgresql/12/main/postgresql.conf’
— check
Если ошибок нет, проводим миграцию данных (если у Вас нет необходимости в копировании файлов в новый кластер, то используйте параметр — link. Будут использованы жесткие ссылки на старый кластер, без копирования):
/usr/lib/postgresql/12/bin/pg_upgrade
— old-datadir=/var/lib/postgresql/11/main
— new-datadir=/var/lib/postgresql/12/main
— old-bindir=/usr/lib/postgresql/11/bin
— new-bindir=/usr/lib/postgresql/12/bin
— old-options ‘-c config_file=/etc/postgresql/11/main/postgresql.conf’
— new-options ‘-c config_file=/etc/postgresql/12/main/postgresql.conf’
Возвращаемся к обычному пользователю
exit
Ваш старый PostgreSQL, скорее всего, использовал порт 5432, а для нового, по-умолчанию, используется 5433. Меняем друг на друга.
sudo vim /etc/postgresql/12/main/postgresql.conf
# меняем "port = 5433" на "port = 5432"
sudo vim /etc/postgresql/11/main/postgresql.conf
# меняем "port = 5432" на "port = 5433"
Запускаем PostgreSQL
sudo systemctl start postgresql.service
Работаем от пользователя postgres
sudo su postgres
Проверяем версию запущенного PostgreSQL
psql -c "SELECT version();"
На новом кластере нет никакой статистики. Нужно запустить ANALYZE по кластеру. Для этого pg_upgrade создал скрипт analyze_new_cluster.sh. Запускаем.
./analyze_new_cluster.sh
Возвращаемся к обычному пользователю
exit
Смотрим, какие старые версии PostgreSQL остались в системе.
apt list --installed | grep postgresql
Удаляем старые версии PostgreSQL, например:
sudo apt-get remove postgresql-11
Удаляем старую конфигурацию:
sudo rm -rf /etc/postgresql/11/
В последний раз заходим под пользователем postgres
sudo su postgres
Удаляем данные старого кластера
./delete_old_cluster.sh
Обновление завершено!
Источник: https://dmitry-naumenko.medium.com/%D0%BE%D0%B1%D0%BD%D0%BE%D0%B2%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5-postgresql-%D1%81-11-%D0%B4%D0%BE-12-%D0%B2%D0%B5%D1%80%D1%81%D0%B8%D0%B8-b35fa87a0c35
Идея, стоящая за методом дампа, заключается в генерации текстового файла с командами SQL, которые при выполнении на сервере, пересоздадут базу данных в том же самом состоянии, в котором она была на момент создания дампа. PostgreSQL предоставляет для этой цели программную утилиту pg_dump. Базовый форма команды выглядит так:
pg_dump имя_БД > файл_дампа
то-есть, pg_dump записывает результаты своей работы на стандартный вывод. Далее будет рассмотрено как из этого можно извлечь пользу.
pg_dump является для PostgreSQL обычным клиентским приложением. Процедура резервного копирования может выполняться с любого удалённого компьютера, который имеет доступ к нужной базе данных. Эта утилита должна иметь доступ на чтение всех таблиц базы данных, резервную копию которых вы хотите сделать, так что на практике её почти всегда нужно запускать с правами суперпользователя СУБД.
Чтобы указать, к какому серверу должен подключаться pg_dump, необходимо использовать опцию командной строки -h сервер и -p порт. По умолчанию, в качестве сервера выбирается localhost или тот сервер, что указан в переменной окружения PGHOST. Похожим образом, по умолчанию используется порт, указанный в переменной окружения PGPORT или, если переменная не заданна, то порт, указанный по умолчанию при компиляции.
Как и любое другое клиентское приложение PostgreSQL, pg_dump по умолчанию будет подключаться к базе данных, под пользователем, имя которого совпадает с именем текущего пользователя в операционной системе. Чтобы изменить пользователя необходимо использовать опцию -U, либо установить нужное значение переменной окружения PGUSER.
Важное преимущество pg_dump над другими методами резервного копирования состоит в том, что базы данных, сохраненные при помощи pg_dump, могут быть залиты в более новые версии PostgreSQL, в то время как резервная копия на уровне файловой системы (простое копирование файлов баз данных) являются жёстко зависимыми от версии сервера.
Также, только pg_dump является методом, который будет работать при переносе базы данных на другую машинную архитектуру, например, при переносе с 32-битной на 64-битную версию сервера.
Дампы, создаваемые pg_dump являются внутренне целостными, что означает, что дамп представляет собой снимок базы данных на момент начала запуска pg_dump. pg_dump не блокирует другие операции с базой данных во время своей работы.
Если схема базы данных полагается на OID (например, как внешние ключи), вы должны сказать pg_dump, чтобы в дамп были также включены OID. Чтобы сделать это, используйте опцию командной строки -o.
Команда pg_dump может сохранять резервную копию базы в двух форматах: в формате текстовых файлов, содержащих набор команд SQL и специальный формат дампа. Если PostgreSQL была скомпилирована в системе с установленной библиотекой zlib, то специальный формат дампа будет сжимать данные, которые выдаются в файл вывода. Это приведёт к созданию файла дампа, который по размеру будет похож на дамп, сжатый gzip, но такой формат будет иметь преимущество, потому что позволяет выборочное восстановление таблиц. Следующая команда делает дамп базы данных, используя специальный формат дампа:
pg_dump -Fc имя_БД > имя_файла
В принципе можно сжать и текстовый формат резервной копии используя стандартные инструменты Linux — ипользовать программу сжатия, например gzip:
pg_dump имя_БД | gzip > имя_файла.gz
распаковывая впоследствии сжатый дамп командой:
gunzip -c имя_файла.gz | psql имя_БД
или:
cat имя_файла.gz | gunzip | psql имя_БД
При больших базах данных и нежелании использовать сжатие можно использовать команду split. Команда split позволяет разбивать текстовые файлы на файлы меньшего размера, которые не попадают под ограничения на максимальный размер файла в файловой системе. Например, чтобы нарезать дамп на кусочки по 1 мегабайту:
pg_dump имя_БД | split -b 1m - имя_файла
Загружая впоследствии полученные файлы командой:
cat имя_файла* | psql имя_БД
Восстановление резервных копий баз PostgreSQL
Текстовые файлы резервных копий баз данных PostgreSQL, содержащие команды sql, предназначаются для последующего чтения программой psql, то-есть выполнения сгенерированной последовательности скриптов. Общий вид команды для восстановления дампа:
psql имя_БД < файл_дампа
где файл_дампа — это файл, содержащий вывод команды pg_dump. База данных, заданная параметром имя_БД не будет создана данной командой, так что ее необходимо предварительно создать из шаблона базы template0 перед запуском psql, например, с помощью команды:
createdb -T template0 имя_БД
psql поддерживает опции для указания сервера, к которому осуществляется подключение и имени пользователя, похожие на pg_dump.
Перед восстановлением SQL дампа, все пользователи, которые владеют объектами или имеют права на объекты в базе данных, выгруженной в дамп, должны уже существовать. Если их нет, при восстановлении будут ошибки пересоздания объектов с оригинальными владельцами и/или правами.
По умолчанию, если произойдёт ошибка SQL, программа psql продолжит своё выполнение. Можно запустить psql с установленной переменной ON_ERROR_STOP, чтобы заставить psql в случае возникновения ошибки SQL завершить работу с кодом 3:
psql --set ON_ERROR_STOP=on имя_БД < файл_дампа
В любом случае база данных будет только частично восстановлена. В качестве альтернативы можно задать, что-бы весь дамп должен быть восстановлен в одной транзации, так что восстановление или будет полностью выполненно или полностью не выполнено. Данный режим может быть задан, с помощью опций командной строки -1 или —single-transaction для psql.
Возможность pg_dump и psql писать и читать из конвееров, делают возможным создание дампа базы данных напрямую с одного сервера на другой, например:
pg_dump -h сервер1 имя_БД | psql -h сервер2 имя_БД
Дампы, которые делает pg_dump являются относительными template0. Это означает, что любые языки, процедуры и т.д. добавленные через template1, также попадут в дамп при выполнении pg_dump. В итоге, при восстановлении, если вы использовали специально изменённый template1, вы должны создать пустую базу данных из template0, как показано в примере выше.
После восстановления резервной копии, очень рекомендуется запустить ANALYZE на каждую базу данных для того, чтобы оптимизатор запросов получил нужную статистику.
Специальный формат дампа не является скриптом для psql и должен восстанавливаться с помощью команды pg_restore, например:
pg_restore -d имя_БД имя_файла
Для очень больших баз данных, вам может понадобиться сочетать split с одним из двух других методов.
pg_dump делает дамп только одной базы данных и не включает в дамп информацию о ролях или табличных пространствах (потому что эти данные относятся скорее к уровню кластера, чем к самой базе данных). Для создания резервной копии всего содержимого кластера баз данных, существует программа pg_dumpall. pg_dumpall делает резервную копию каждой базы данных кластера, а также служебные данные уровня кластера, такие как роли и определения табличных пространств. Базовая форма использования этой команды:
pg_dumpall > файл_дампа
Результирующий дамп может быть восстановлен с помощью psql:
psql -f файл_дампа postgres
При восстановлении дампа, сделанного pg_dumpall, всегда необходимо, выполнять восстановление с правами суперпользователя баз данных, потому что они требуются для восстановления ролей и информации о табличных пространствах.
Источник: https://www.oslogic.ru/knowledge/718/rezervnoe-kopirovanie-i-vosstanovlenie-baz-dannyh-postgresql/
Когда мы говорим о PostgreSQL, мы всегда думаем о базах данных, потому что это система управления базами данных. Вы можете создать на своем сервере PostgreSQL столько баз данных, сколько захотите. После создания базы данных по вашему выбору вы можете создать в ней несколько таблиц для хранения ваших данных, а затем вы можете выполнять различные вычисления с этими данными. Однако иногда мы хотим выполнить запрос к существующей базе данных, но для этого наличие такой базы данных обязательно.
Мы хотим сказать, что нам нужен механизм для проверки, существует ли база данных на нашем сервере PostgreSQL или нет. Более того, бывают ситуации, когда мы хотим, чтобы база данных создавалась после выполнения запроса, если она еще не существует на нашем сервере базы данных. В этом случае в игру вступает нотация «Создать базу данных, если не существует». Это руководство призвано познакомить вас с использованием этой нотации с кратким обсуждением того, поддерживает ли PostgreSQL эту нотацию или нет. После этого мы поделимся с вами альтернативой этой нотации, поддерживаемой PostgreSQL.
Обозначение «Создать базу данных, если не существует» поддерживается некоторыми языками программирования. С помощью этой нотации вы можете проверить, существует ли указанная база данных на вашем сервере баз данных или нет, и если она не существует, то эта нотация просто создаст эту базу данных на вашем сервере. Однако поговорим конкретно о PostgreSQL. PostgreSQL не поддерживает эту нотацию, или, другими словами, вы можете сказать, что мы не можем использовать эту нотацию непосредственно в PostgreSQL в Windows 10.
Тем не менее, есть еще несколько способов, которые позволят вам достичь той же функциональности, которую вы можете достичь с помощью этой конкретной нотации. Чтобы узнать больше об этих обходных путях, вам придется продолжить чтение этой статьи.
Поскольку нотация «Создать базу данных, если не существует» не может использоваться в среде PostgreSQL, поэтому мы решили поделиться с вами обходным путем, с помощью которого вы можете достичь той же функциональности. Для этого обходного пути вам нужно будет выполнить несколько иной вариант этой нотации в форме запроса 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.
Теперь, когда мы увидели, что база данных, которую мы хотим создать, еще не существует на нашем сервере 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=#
Если вы все еще не уверены, что ваша попытка создания новой базы данных 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.