Error message from server error invalid memory alloc request size - Исправление ошибок и поиск оптимальных решений проблем

Содержание

«invalid memory alloc request size» from PostgreSQL with large query results #4918
Comments
Issue Summary
Steps to Reproduce
Technical details:
Footer
«invalid memory alloc request size» from PostgreSQL with large query results #4918
Comments
Issue Summary
Steps to Reproduce
Technical details:
Footer
MS Sql -> Postgres
MS Sql -> Postgres
Thread: standby replication server throws invalid memory alloc request size ,does not start up
standby replication server throws invalid memory alloc request size ,does not start up

«invalid memory alloc request size» from PostgreSQL with large query results #4918

Issue Summary

I’m not sure if it’s directly related to #78, so I start a new issue.

When a query returns too much data, we get an explicit PostgreSQL error when Redash tries to insert the query result into its query_results table.

Steps to Reproduce

Run a query returning a lot of data (5379488 rows and 260MB of data in our case)

Few seconds later, PostgreSQL returns in its log:

1 minute), the UI reports «Error running query: failed communicating with server. Please check your Internet connection and try again.»

Note that is not only about the number of rows returned, querying only one column over all the rows works.

I didn’t find any PostgreSQL settings to tweak to get through this error. After some investigations, the error comes from palloc functions: https://doxygen.postgresql.org/palloc_8h.html#a7d104f43412595fb2d3ca12963084f23

What I still didn’t understand is why Redash/PostgreSQL requires more than 1GB memory to insert only 260MB of query result.

Technical details:

Redash Version: redash/redash:8.0.0.b32245
How did you install Redash: Docker/docker-compose, following the official procedure.

The text was updated successfully, but these errors were encountered:

What I still didn’t understand is why Redash/PostgreSQL requires more than 1GB memory to insert only 260MB of query result.

How did you measure the size of the dataset? Anyway, the difference can be because of the serialization used (JSON, each row is an object).

Also, I’m not sure this error is about exceeding 1gb, but more about just not having enough memory to allocate.

Note that is not only about the number of rows returned, querying only one column over all the rows works.

Of course, it’s about the total size of the dataset.

Beside the general need to support better large data sets, the issue described here is more about your system configuration than anything in the code.

How did you measure the size of the dataset? Anyway, the difference can be because of the serialization used (JSON, each row is an object).

I just dumped the table and got the file size. Of course it’s more to get a rough idea of how big the dataset is, not an accurate value.

Also, I’m not sure this error is about exceeding 1gb, but more about just not having enough memory to allocate.

That’s what I thought first as I ran through this error, but Redash runs on a public cloud instance with a comfortable 14GB total memory, on Debian buster without memory or other resources limits.

So, I managed to capture the whole query sent to PostgreSQL. The INSERT is 606MB big. I then tried to replay the INSERT on multiple environments: PostgreSQL 9.6 or 11, alpine/standard image, docker or bare-metal, on other servers. The query fails in all scenarios with the same error. I think we hit a limit somewhere in PostgreSQL.

I close the issue since it’s only a consequence of #78

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

Источник

«invalid memory alloc request size» from PostgreSQL with large query results #4918

Issue Summary

I’m not sure if it’s directly related to #78, so I start a new issue.

When a query returns too much data, we get an explicit PostgreSQL error when Redash tries to insert the query result into its query_results table.

Steps to Reproduce

Run a query returning a lot of data (5379488 rows and 260MB of data in our case)

Few seconds later, PostgreSQL returns in its log:

1 minute), the UI reports «Error running query: failed communicating with server. Please check your Internet connection and try again.»

Note that is not only about the number of rows returned, querying only one column over all the rows works.

What I still didn’t understand is why Redash/PostgreSQL requires more than 1GB memory to insert only 260MB of query result.

Technical details:

Redash Version: redash/redash:8.0.0.b32245
How did you install Redash: Docker/docker-compose, following the official procedure.

The text was updated successfully, but these errors were encountered:

What I still didn’t understand is why Redash/PostgreSQL requires more than 1GB memory to insert only 260MB of query result.

How did you measure the size of the dataset? Anyway, the difference can be because of the serialization used (JSON, each row is an object).

Also, I’m not sure this error is about exceeding 1gb, but more about just not having enough memory to allocate.

Note that is not only about the number of rows returned, querying only one column over all the rows works.

Of course, it’s about the total size of the dataset.

Beside the general need to support better large data sets, the issue described here is more about your system configuration than anything in the code.

How did you measure the size of the dataset? Anyway, the difference can be because of the serialization used (JSON, each row is an object).

I just dumped the table and got the file size. Of course it’s more to get a rough idea of how big the dataset is, not an accurate value.

Also, I’m not sure this error is about exceeding 1gb, but more about just not having enough memory to allocate.

That’s what I thought first as I ran through this error, but Redash runs on a public cloud instance with a comfortable 14GB total memory, on Debian buster without memory or other resources limits.

I close the issue since it’s only a consequence of #78

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

Источник

MS Sql -> Postgres

Есть 2 базы. БУХ 3.0 и УПП 1.3
Перенос проводил через выгрузку загрузку .DT с предварительным ТИИ .
Бух 3.0 нормально перенеслась из MS SQL в Postgres . Бэкапится и работает.

УПП 1.3: При проведении документа ОШИБКА СУБД cache lookup failed for function 0
Поиски привели к необходимости сделать Дамп/Бэкап
При попытке сделать Бэкап, ошибка:
pg_dump: saving standard_conforming_strings = off

pg_dump: saving search_path =

pg_dump: saving database definition

pg_dump: [archiver (db)] query failed: ERROR: invalid memory alloc request size 1191164729

pg_dump: [archiver (db)] query was: FETCH 100 FROM _pg_dump_cursor

(1)
Доброго дня.
Аналогично пытался перенести упп на postgres. База около 30 гиг размером.
PG с сайта 1с последний. Предприятие оттуда же, 8.3.15.
Получил такую же ошибку.
Дамп делал, не помогло.
Так же пробовал делать тестирование и исправление. Все проходит успешно, если не ставить галку «реструктуризация таблиц». На этом падает.
Но эффект от этих действий нулевой.
Пришлось вернуться к mssql.

Осталось попробовать только pg_dumpall.

Тоже перенес на Postgres
8.3.16.1148 конфиг бухгалтерия 1.2
база маленькая, места навалом, проверка и рестурктуризация на ура
А вот отмена проведения произвольного документа (не всех) вываливает эту ошибку

Нагуглил что это связано с шаманствами преобразования строк mvarchar — но как исправить не нашел

Бухгалтерия 2.0
Очередное обновление потребовало обновление платформы. Заодно, решили обновить PostgreSQL.
И так, платформа 8.3.16.1148 и PostgreSQL 11.5-12
В итоге, на многих операциях (проведение документа, пометка на удаление и тп) — ОШИБКА СУБД cache lookup failed for function 0

Пришлось понизить версию PostgreSQL до 10.10-4

Поимел такую же ошибку (ОШИБКА СУБД cache lookup failed for function 0) на УПП 1.3, релиз 1.3.134.1, платформа 8.3.15.1489, постгрес 11.5-12.1С(х64).
База небольшая, dt 1.3гб. *.dt выгружается, на файловой базе ошибки нет. Конфигурация почти типовая, но без замочка.

Использовал различные танцы с бубном навроде: майнтенанс базы pgAdmin-ом, ТИИ, вкуривание логов постгре, изменение регистра бухгалтерии туда-сюда (для инициирования реструктуризации и пересоздания таблиц в постгре), удаления проблемной таблицы средствами постгре. Логи постгре говорят, что проблема в таблице итогов регистра бухгалтерии по субконто _AccRgAT21653. Но что с этой таблицей ни делай, ошибка возвращается.

РЕШЕНИЕ:
поставил в конфигурации режим совместимости «Версия 8.3.9». Да, появляются артефакты, вроде дублирования имен картинок (которые в 8.3.9 стали системными) и некоторых функций общего модуля ОбщийМодуль.ИнтеграцияЕГАИСУТКлиентСерверГлобальный.Модуль(8,9)>: Процедура или функция с указанным именем уже определена (СтрНайти) — нужно переименовывать функции.
Но проблема с cache lookup failed ушла. Прогонял разные документы — пока все ровно. Буду наблюдать далее.

BTW: УПП в дефолтовом режиме совместимости весьма плохо дружит с постгре в плане производительности. на большой базе с количеством пользователей около 100 было невозможно работать, пока не поднял режим совместимости. После этого платформа 1с начинает генерировать адекватные запросы в постгре.

Источник

MS Sql -> Postgres

pg_dump: saving search_path =

pg_dump: saving database definition

pg_dump: [archiver (db)] query failed: ERROR: invalid memory alloc request size 1191164729

pg_dump: [archiver (db)] query was: FETCH 100 FROM _pg_dump_cursor

Осталось попробовать только pg_dumpall.

Нагуглил что это связано с шаманствами преобразования строк mvarchar — но как исправить не нашел

Пришлось понизить версию PostgreSQL до 10.10-4

Источник

Thread: standby replication server throws invalid memory alloc request size ,does not start up

standby replication server throws invalid memory alloc request size ,does not start up

This is my first postgres query to admin list, so if I am not following the right standards for asking the question, pls let me know рџЉ

I have a postgres cluster as

A (primary)-> streaming replication -> B(hot_standby=on)

We had a power outage in one of the data centers, and when we got back, one of the databases servers (B the standby node) seem to show weird errors and is not starting up. A recovered fine, and it running fine.

2018-06-25 10:57:04 UTC HINT:В In a moment you should be able to reconnect to the database and repeat your command.

2018-06-25 10:57:04 UTC WARNING:В terminating connection because of crash of another server process

2018-06-25 10:57:04 UTC DETAIL:В The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2018-06-25 10:57:04 UTC HINT:В In a moment you should be able to reconnect to the database and repeat your command.

2018-06-25 10:57:04 UTC LOG:В database system is shut down

2018-06-27 16:59:28 UTC LOG:В listening on IPv4 address «0.0.0.0», port 5432

2018-06-27 16:59:28 UTC LOG:В listening on IPv6 address «::», port 5432

2018-06-27 16:59:28 UTC LOG:В listening on Unix socket «/var/run/postgresql/.s.PGSQL.5432»

2018-06-27 16:59:28 UTC LOG:В database system was interrupted while in recovery at log time 2018-06-25 10:52:21 UTC

2018-06-27 16:59:28 UTC HINT:В If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.

2018-06-27 16:59:28 UTC LOG:В entering standby mode

2018-06-27 16:59:28 UTC LOG:В recovered replication state of node 1 to 63/89B52A98

2018-06-27 16:59:28 UTC LOG:В redo starts at 9A/6F1B3888

2018-06-27 16:59:28 UTC LOG:В consistent recovery state reached at 9A/6F25BFF0

2018-06-27 16:59:28 UTC FATAL:В invalid memory alloc request size 1768185856

2018-06-27 16:59:28 UTC LOG:В database system is ready to accept read only connections

2018-06-27 16:59:28 UTC LOG:В startup process (PID 11829) exited with exit code 1

2018-06-27 16:59:28 UTC LOG:В terminating any other active server processes

2018-06-27 16:59:28 UTC LOG:В database system is shut down

pg_ctl: could not start server

Examine the log output.

I did a lot of searching around вЂњinvalid memory alloc requestвЂќ it mostly points to some data error,

mem checks and disks are ok.

I tookВ a filesystem snapshot, synced it onto another server and tried to reload it, it gave the same error.

Now to ensure there was not real data causing this issue, I took a full dump of the primary db (A) using pg_dumpall and restored in onto the test server. I was able to load the db and was able to query the tables without issues.

So I am confused why the standby is behaving weird after the power outage.

Just FYI, I spun up a new node ( В CВ ) as a replacement for the problem standby(B) with pg_baseback and it is working fine. Can query all tables without problems. My only concern was do I need to worry about this error showing up again?

Most the googling points to some operation and finding out errors in some operation performed etc,

I ran that function (in 1), could not find bad data rows.

Also, I tried to bump up (memory to 32GB, cpu to 8 cpu and shmmax to В 18446744073692774399 to figure out is there was any resource constraint that I could temp bump up and then debug what went wrong, but it did not start up.

postgres (PostgreSQL) 10.4 (Ubuntu 10.4-2.pgdg16.04+1)

Источник

After 3 days of running an import routine written on RoR to move some 269,000 records from a legacy database via csv files the routine finally finished.
The very first thing I attempt to do is to backup the database using pg_dump before doing anything else.

This resulted in the following

pg_dump -Ft hsb > hsb_after_full_import.tar

pg_dump: Dumping the contents of table "companies" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:  invalid memory alloc request size 18446744073709551613
pg_dump: The command was: COPY public.companies (id, category_id, city_id, name, address, town, neighbourhood, post_code, lon, lat, contact, email, facebook, twitter, listing_url, image_url, phone, website, listing_cid, promotion, description, rating, reviews, wheelchair_accessible, merchant_verified, uploaded, created_at, updated_at, server_id, opening_hours, multinational, places_id, featured_image_file_name, featured_image_content_type, featured_image_file_size, featured_image_updated_at, places_lat, places_lon, source, sanitised) TO stdout;

I am unable to find a tool to verify and fix any potential corruption. Indeed I have encountered many suggestions that Postgresql doesn’t get corrupted (I find that impossible to believe) and therefore needs no repair/verification tools.

The import went smoothly and many tests of a few thousand records were undertaken before the full run to determine the suitability of the import script.

There is no point re-creating the database and re-importing if Postgresql is unable to handle this amount of data and just corrupt again.

I would really appreciate some guidance in the best way to proceed? Maybe an alternative backup tool?

The data appears to be OK. The app that is using the data seems to behave itself properly, maybe it’s just the pure size of the dump and maybe an issue with the amount of ram (4gb) I have on my machine?

PostgreSQL version

pg_config --version
PostgreSQL 9.3.12

The database was created using Rails db rake task

rake db:create

From PGAdmin III the create script looks like this

-- Table: companies

-- DROP TABLE companies;

CREATE TABLE companies
(
  id serial NOT NULL,
  category_id integer,
  city_id integer,
  name character varying(255),
  address character varying(255),
  town character varying(255),
  neighbourhood character varying(255),
  post_code character varying(255),
  lon character varying(255),
  lat character varying(255),
  contact character varying(255),
  email character varying(255),
  facebook text,
  twitter text,
  listing_url character varying(255),
  image_url character varying(255),
  phone character varying(255),
  website text,
  listing_cid character varying(255),
  promotion text,
  description text,
  rating character varying(255),
  reviews integer,
  wheelchair_accessible boolean NOT NULL DEFAULT true,
  merchant_verified boolean NOT NULL DEFAULT true,
  uploaded boolean NOT NULL DEFAULT true,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  server_id integer,
  opening_hours text,
  multinational boolean DEFAULT false,
  places_id character varying(255),
  featured_image_file_name character varying(255),
  featured_image_content_type character varying(255),
  featured_image_file_size integer,
  featured_image_updated_at timestamp without time zone,
  places_lat double precision,
  places_lon double precision,
  source character varying(255),
  sanitised boolean DEFAULT false,
  CONSTRAINT companies_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE companies
  OWNER TO wpsmiler;

I’m running Linux mint 17.3

select pg_total_relation_size(‘companies’) gives me 113459200

If I run the query manually I get some records output (I assume 100 because of the message) then the same error

Query returned more than 100 copy rows, discarding the rest...

ERROR:  invalid memory alloc request size 18446744073709551613
********** Error **********

ERROR: invalid memory alloc request size 18446744073709551613
SQL state: XX000

Источник

I’m building a website which will be used to handle excel files from stores and manipulate them (merging, view, etc.). I’m using PostgreSQL 9.4 for the database, running on Centos 6.6 VM with 4GB RAM. It has 3 databases as follow:

postgres database
db_raw, which is used as a placeholder for the data. The excel which are uploaded from the website will be parsed, and the data will be stored here. The database consists of few tables used to keep the data required to process the excel, and a huge table for storing the excel data with currently >140 column and almost 1 million row
db_processed, which is the main database for the website. It has few small tables for the operational of the website (user table, access list, logging, etc), and 8 tables to store the processed excel data from db_raw. Each of the 8 tables have around 40 column and about a million row.

The databases were running fine until this morning. I tried connecting to the db_processed through pgAdmin and PuTTY, and PostgreSQL gave me this message

FATAL:  invalid memory alloc request size 144115188075856068

db_raw works fine, and nothing has been changed since 3 days ago as far as I know. What should I do so I can connect to the database again?

update : I did what @CraigRinger said and restarted the service. I manage to connect to the database, but all the tables are gone now this keeps appearing in the log

< 2015-09-21 12:27:22.155 WIB >DEBUG:  performing replication slot checkpoint
< 2015-09-21 12:27:22.158 WIB >LOG:  request to flush past end of generated WAL; request 46/9E0981D8, currpos 46/771C69B0
< 2015-09-21 12:27:22.158 WIB >CONTEXT:  writing block 2 of relation base/18774/12766
< 2015-09-21 12:27:22.158 WIB >ERROR:  xlog flush request 46/9E0981D8 is not satisfied --- flushed only to 46/771C69B0
< 2015-09-21 12:27:22.158 WIB >CONTEXT:  writing block 2 of relation base/18774/12766
< 2015-09-21 12:27:22.158 WIB >WARNING:  could not write block 2 of base/18774/12766
< 2015-09-21 12:27:22.158 WIB >DETAIL:  Multiple failures --- write error might be permanent.

asked Sep 21, 2015 at 4:52

It is caused by corrupted rows.

Create a function do «detect» the rows that are corrupted:

CREATE OR REPLACE FUNCTION is_bad_row(tableName TEXT, tabName TEXT, tidInitial tid)
RETURNS integer 
as $find_bad_row$
BEGIN
    EXECUTE 'SELECT (each(hstore(' || tabName || '))).* FROM ' || tableName || ' WHERE ctid = $1' USING tidInitial; 
    RETURN 0;
    EXCEPTION
        WHEN OTHERS THEN
        RAISE NOTICE '% = %: %', tidInitial, SQLSTATE, SQLERRM;
        RETURN 1;
END
$find_bad_row$
LANGUAGE plpgsql;

… and then create a «temp table» to store the ctid of the bad rows:

create table bad_rows as 
SELECT ctid as row_tid
FROM your_schema.your_table
where is_bad_row('your_schema.your_table', 'your_table', ctid) = 1

… and after that you just need to delete those rows:

delete from your_schema.your_table where ctid in (select row_tid from bad_rows)

… and remove the «temp table»:

drop table bad_rows

answered May 28, 2020 at 17:57

ChristianChristian

6,9449 gold badges51 silver badges79 bronze badges

Источник

В этой статье разберем оптимизацию работы с моментальным снимком отдельной базы 1С в кластере PostgreSQL средствами pg_dump.exe, pg_restore.exe, psql.exe в среде Windows Server 2008,2012,2023. А также разберем проблемные ситуации и неожиданные ограничения при работе 1С в связке с PostgreSQL. Для Linux все аналогично.

Копирование-восстановление с помощью моментального снимка базы может потребоваться в разных случаях:
— копирование в другую базу в пределах кластера
— копирование в другую базу на другом сервере с более высокой версией PostgreSQL
— восстановление текущей базы
— восстановление некоторых таблиц текущей базы в случае падения 1С и т.д.

Задача 1. Копирование базы 1С на лету во время работы пользователей с сохранением целостности с помощью команд pg_dump.exe, psql.exe

Для этого в общем случае алгоритм следующий:

Шаг 1. Выгружаем с помощью pg_dump.exe все таблицы из базы данных, кроме данных таблицы config (т.е. для config выгружаем только ее схему)

Шаг 2. Выгружаем с помощью psql.exe данные таблицы public.config с помощью COPY WITH BINARY

Итак,

— Шаг 1. Выгружаем с помощью pg_dump.exe все таблицы из базы данных, кроме данных таблицы config (т.е. для config выгружаем только ее схему):

«<путь к pg_dump>pg_dump.exe» и далее параметры с комментариями

—host <ip адрес или имя хоста сервера>

—port <порт>

—username «postgres» – имя пользователя

—role «postgres» — роль

—no-password – не спрашивать пароль в пакетном режиме

—format directory – создавать архив в виде каталога для использования параметра —jobs. При этом каждая таблица копируется в отдельный файл в каталоге, что позволяет распараллелить процесс создания архива

—jobs=<количество параллельных потоков процессора> — подбирается примерно как <количество ядер процессора>*2, можно пробовать увеличение или уменьшение параметра, чтобы максимально загрузить систему, не мешая работе пользователей. Практически на каждый процесс запускается копирование одной таблицы из базы данных. Сколько процессов задействовано, столько таблиц и обрабатывается одновременно. С помощью этого параметра можно достигнуть ускорения резервного копирования в 4 раза и более в зависимости от мощности оборудования сервера.

—blobs – позволяет выгружать поля большого размера

—encoding UTF8 — кодировка

—verbose – включить подробное комментирование

—exclude-table-data=config — исключить из выгрузки данные таблицы config, т.е. выгрузить только ее схему (config содержит записи: конфигурация, конфигурации поставщиков, отличия основной конфигурации от конфигураций поставщиков). Это требуется, когда база находится на поддержке у двух и более конфигураций поставщика и (или) очень много изменений внесено в конфигурацию. При этом размер изменений основной конфигурации относительно конфигурации одного из поставщиков приближается к 1Гб, что является пределом для поля большого размера в PostgreSQL. А 1С хранит изменения только в одной из записей таблицы config. При небольшом размере конфигурации можно не использовать этот параметр. Но при критическом (если размер хотя бы одной записи таблицы public.config (конфигурации) после чтения и распаковки в стандартный поток вывода stdout превысит 1 Гб) pg_dump.exe завершится с ошибкой:

pg_dump: Ошибка выгрузки таблицы «config»: сбой в PQgetResult().
pg_dump: Сообщение об ошибке с сервера: invalid memory alloc request size 11173708065
pg_dump: Выполнялась команда: COPY public.config (filename, creation, modified, attributes, datasize, binarydata) TO stdout;

Если используется —format custom, то архив выгружается в виде одного файла, и ошибка создания архива на таблице public.config обнаружится при выполнении команды pg_restore, что и есть самое неприятное:

pg_restore: обрабатываются данные таблицы «public._usersworkhistory»
pg_restore: обрабатываются данные таблицы «public._yearoffset»
pg_restore: обрабатываются данные таблицы «public.config»
pg_restore: [внешний архиватор] не удалось прочитать входной файл: конец файла

(кроме того —format custom не позволит использовать —jobs — распараллеливание)

Наблюдается на больших конфигурациях KA 1.1-2.4, УПП 1.3, ERP 2.4.

—file «<имя каталога архива без таблицы config>»

«<имя базы данных>»

Шаг 2. Выгружаем с помощью psql.exe данные таблицы public.config с помощью COPY WITH BINARY:

md «<имя каталога архива только с таблицей config >» — создаем каталог для таблицы config

«<путь к psql>psql.exe» – далее параметры

—host <ip адрес или имя хоста сервера>

—port <порт>

—username «postgres» – имя пользователя

—no-password – не спрашивать пароль в пакетном режиме

—command «COPY public.config TO ‘<имя каталога архива только с таблицей config с разделителями для винды>’ WITH BINARY;»

—dbname=»<имя базы данных>»

Задача 2. Восстановление базы 1С в копию во время работы пользователей с сохранением целостности с помощью команд pg_restore.exe, psql.exe. Для этого в общем случае алгоритм следующий:

Шаг 0. Создаем пустую копию базы средствами pgAdmin.exe или с помощью psql.exe, dropdb, createdb.

Шаг 1. Загружаем с помощью pg_restore.exe все таблицы из архива базы данных, кроме данных таблицы config (т.е. для config загружаем только ее схему)

Шаг 2. Загружаем с помощью psql.exe данные таблицы public.config с помощью COPY WITH BINARY

Шаг 0. Создаем пустую копию базы средствами pgAdmin.exe или с помощью psql.exe, dropdb, createdb.

Если база данных существует, мы должны сначала удалить ее из кластера с помощью команды DROP DATABASE «<имя базы данных>», а затем создать заново с помощью команды CREATE DATABASE «<имя базы данных>». Проще всего это сделать через pgAdmin, так как в нем есть запрос на удаление базы и образец запроса на создание базы. При удалении базы необходимо закрыть все соединения с базой, иначе база не будет удалена. Для Windows запрос на создание базы выглядит так:

CREATE DATABASE «<имя базы данных>»

WITH

OWNER = postgres

ENCODING = ‘UTF8’

LC_COLLATE = ‘Russian_Russia.1251’

LC_CTYPE = ‘Russian_Russia.1251’

TABLESPACE = pg_default

CONNECTION LIMIT = -1;

Если мы время от времени хотим проверять, что база корректно достается из архива, то имеет cмысл автоматизировать шаг 0 в батнике командами psql, dropdb, createdb (добавлено по просьбам читателей):

0.1 Перед удалением закрываем все активные соединения с базой <имя базы данных>, кроме текущего:

«<путь к psql>psql.exe» – далее параметры

—host <ip адрес или имя хоста сервера>

—port <порт>

—username «postgres» – имя пользователя

—dbname=»<имя базы данных>»

—no-password – не спрашивать пароль в пакетном режиме

—command «SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = ‘<имя базы данных>’ AND pid <> pg_backend_pid();»

0.2 Удаляем базу <имя базы данных>, если она существует без подтверждения об удалении.
«<путь к dropdb>dropdb.exe»

—host <ip адрес или имя хоста сервера>

—port <порт>

—username «postgres» – имя пользователя

—no-password – не спрашивать пароль в пакетном режиме

—if-exists — проверка существования базы

—echo — вывод команд SQL, которые выполнит postgreSQL при вызове

«<имя базы данных>»

0.3 Создаем заново базу <имя базы данных> после удаления
«<путь к createdb>createdb.exe»

—host <ip адрес или имя хоста сервера>

—port <порт>

—username «postgres» – имя пользователя

—no-password – не спрашивать пароль в пакетном режиме

—echo — вывод команд SQL, которые выполнит postgreSQL при вызове

—owner=»postgres» — владелец базы

—encoding=»UTF8″ — кодировка

—locale=»Russian_Russia.1251″ — устанавливает одновременно параметры LC_COLLATE и LC_CTYPE для базы данных.

—tablespace=»pg_default» — указывает табличное пространство, используемое по умолчанию.

«<имя базы данных>«

Теперь уже не нужно даже лезть в pgAdmin и там корячиться.

Шаг 1. Загружаем с помощью pg_restore.exe все таблицы из архива базы данных, кроме данных таблицы config (т.е. для config загружаем только ее схему):

«<путь к pg_restore>pg_restore.exe»

—host <ip адрес или имя хоста сервера>

—port <порт>

—username «postgres» – имя пользователя

—role «postgres» — роль

—no-password – не спрашивать пароль в пакетном режиме

—dbname «<имя базы данных в которую восстанавливаем>»

—jobs=<количество параллельных потоков процессора> — подбирается примерно как <количество ядер процессора>*2, можно пробовать увеличение или уменьшение параметра, чтобы максимально загрузить систему, не мешая работе пользователей. Практически на каждый процесс запускается восстановление одной таблицы базы данных. Сколько процессов задействовано, столько таблиц и индексов обрабатывается одновременно. С помощью этого параметра можно достигнуть ускорения восстановления базы в 2-3 раза и более в зависимости от мощности оборудования сервера.

—verbose – включить подробное комментирование

«<имя каталога архива без таблицы config>»

Шаг 2. Загружаем с помощью psql.exe данные таблицы public.config с помощью COPY WITH BINARY

«<путь к psql>psql.exe» – далее параметры

—host <ip адрес или имя хоста сервера>

—port <порт>

—username «postgres» – имя пользователя

—dbname=»<имя базы данных>»

—no-password – не спрашивать пароль в пакетном режиме

—command «COPY public.config FROM ‘<имя каталога архива только с таблицей config с разделителями для винды>’ WITH BINARY;»

Обратите внимание на метакоманду COPY вместо просто COPY.

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

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

Postgres ERROR: Permission denied (не удалось открыть файл для чтения)

Заметим, что архив базы может быть успешно восстановлен на последующих версиях PostgreSQL. В моем случае, я переносил базу с сервера PostgreSQL 9.6 на PostgreSQL 10.3.

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

На реальной базе размером 380 Гб за счет распараллеливания было достигнуто ускорение при работе pg_dump — в 4 раза, при работе pg_restore — в 2,5 раза, что весьма существенно, когда процесс занимает несколько часов.

Было: 2 часа на копирование, стало 0,5 часа на копирование; было 10 ч на восстановление, стало 4 ч на восстановление.

После перехода на более мощный сервер получили еще более существенные результаты:
Было: 2 часа на копирование, стало 0,5 часа на копирование; было 10 ч на восстановление, стало 1 ч 40 мин на восстановление. То есть количество ядер процессора и более быстрые диски при тех же параметрах дают значительное ускорение. Теперь чтобы скопировать и развернуть нашу огромную базу требуется всего 2 часа!

Ниже привожу примеры bat-файлов (качайте) для создания архивной копии базы данных DO_PROBA и восстановления в другую базу данных DO_PROBA_COPY. При этом в названии каталогов архива используется дата и время начала архивации и выводятся замеры времени на создание-восстановление. При восстановлении из вновь созданного архива необходимо каждый раз менять дату и время в bat-файле для восстановления (ее можно скопировать из имени каталога архива по образцу). Теперь будьте очень осторожны при восстановлении базы. По просьбам читателей и пользователей в bat-файлы добавлены команды автоматического удаления и создания восстанавливаемой базы в случае ее существования. Не ошибитесь в наименовании базы в команде SET ar_base_to=<Имя базы, куда восстанавливаем> !!! Иначе можно легко порушить существующие базы.

СОВЕТ 1: периодически проверяйте (хотя бы раз в неделю) загрузку бэкапа в какую-нибудь тестовую базу и запускайте для проверки работоспособности режим конфигуратора 1С и рабочий режим. Тогда всегда будете уверены в своей архивной копии!

СОВЕТ 2: после отладки копирования и восстановления можно настроить Планировщик заданий (для Windows) на запуск нашего bat-файла по выбранному расписанию. Например, в 3:00 ночи ежедневно, пока никто из пользователей не работает.

*********

С 4 по 6 февраля 2023 года в стенах Московского государственного университета состоится конференция по PostgreSQL – PGConf.Russia 2023. Ежегодно она собирает более 500 разработчиков, администраторов баз данных и IT-менеджеров для обмена опытом и профессионального общения.

На этот раз PGConf.Russia будет особенной. Инфостарт совместно с Postgres Pro организует на конференции секцию «Postgres+1C». Мы приглашаем участников сообщества посетить PGConf и даже выступить в качестве докладчика.

Источник