Ошибка при восстановлении бэкапа postgresql

Postgresql ошибки при восстановлении

Модератор: Дмитрий Юхтимовский

Вернуться в Прочее

Кто сейчас на форуме

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 1

I’m trying to restore backup from Postgres 9.1.1, which was created by:

pg_dump mydb > backup.sql

restore on Postgres 9.1.9

psql -d mydb -f backup.sql

I’m getting this error:

psql:datasets.sql:278537: invalid command \.
psql:datasets.sql:278544: ERROR:  syntax error at or near "1"
LINE 1: 1 4446 49 253.412262 239.618317 0 211.54303 100.482948 197.1...

The \. is part of COPY command, I guess it should be compatible between Postgres 9 versions, or it’s not?

COPY data_136 (id, in_1, in_2, in_3, in_4, out_1) FROM stdin;
1       5.0999999       3.5     1.39999998      0.200000003     Iris-setosa
2       4.9000001       3       1.39999998      0.200000003     Iris-setosa
--- few more line cutted
150     5.9000001       3       5.0999999       1.79999995      Iris-virginica
\.

Доброго дня.
Появилась проблема, и я очень прошу помощи.

Суть.
Есть сервер с CentOS 6, на базе которого крутится postgres.

[root@DataB ~]# psql -V
psql (PostgreSQL) 9.4.2

Там имеются базы данных, которые используются как базы 1С.
На этом сервере есть sh скрипт который выполняется по cron. Суть этого скрипта, это бэкапить базы на NAS. Пример скрипта, приведу ниже.

/usr/pgsql-9.4/bin/pg_dump --host localhost --port 5432 --username "postgres" --role "postgres" --no-password  --format custom --blobs --compress 9 --encoding UTF8 --verbose --file /mnt/NASbackup/Buh_UKEKNew/Buh_UKEKNew-$(date +%Y-%m-%d).backup "Buh_UKEKNew"  2> $LOG_FILE

И вроде всё делается нормально, бэкапы делаются, я спокоен, и пару мепсяцев один из них разворачивал, и тут попытался сделать это снова с помощью…

pg_restore --host localhost --port 5432 --username "postgres" --dbname "Buh_sheregesh_31032021" --role "postgres" --no-password --clean  --section pre-data --section data --section post-data --verbose "/root/Buh_sheregesh3-2021-03-31.backup"

База изначально была создана с помощью консоли 1С.

И вот, после восстановления бэкапа я получаю следующее.

WARNING: errors ignored on restore: 23685
You have mail in /var/spool/mail/root

Другие бэкапы так же пытался восстановить. Количество ерроров меняется, но суть остаётся неизменной.
Я получаю следующее.

И вот полный текст.
Невосстановимая ошибка
Ошибка при выполнении запроса POST к ресурсу /e1cib/login:
по причине:
Ошибка при выполнении операции с информационной базой
Ошибка СУБД:
XX000: ERROR: There are 2 candidates for ‘mchar_pattern_fixed_prefix’ function’

по причине:
Ошибка СУБД:
XX000: ERROR: There are 2 candidates for ‘mchar_pattern_fixed_prefix’ function’

Что то мне подсказывает что это может быть типичная ошибка новичка, но я пока ума не приложу, в какую сторону копать?

PS. Я понимаю что и CentOS старый, и PGSQL. Но пока не было возможности остановить предприятие более чем на 8 часов, что бы всё перенести, а текучку никто не отменял:(

In this article, we will see how to solve "pg_restore: error: input file appears to be a text format dump. Please use psql". As you might be aware that pg_dump and pg_restore are the most widely used utilities to backup and restore PostgreSQL databases. So it is not very uncommon to face an error like this while trying to restore or rebuild the database from the dump which you have taken through pg_dump utility. Something similar happened to me as well when I tried to take the dump of my PostgreSQL cyberithub_db database. Here I am going to explain you more about the error that I faced and the possible solutions that you can use to solve this problem.

Also Read: How to Install Mojo on Ubuntu 20.04 LTS (Focal Fossa)

Now coming back to the error again, usually when you try to restore database using pg_restore utility from the dump you have taken using pg_dump utility then you might see "pg_restore: error: input file appears to be a text format dump. Please use psql" as it happened with me as you can see below.

postgres@ubuntu:~$ pg_restore -d cyberithub_db cyberithub.dump
pg_restore: error: input file appears to be a text format dump. Please use psql.

While above error could occur due to different reasons but most of the time it is because you have not specified the correct format while trying to take the dump of your database using pg_dump utility. For example, in my case this error happened because I forgot to use -F c with pg_dump command while trying to take the dump of my cyberithub_db database as you can see below.

postgres@ubuntu:~$ pg_dump cyberithub_db > cyberithub.dump

If you are also facing above error then to fix it you can think of using below two working solutions depending on your use case scenario.

Solution 1: Use psql to restore

When you do not use the correct format while taking the dump of your database using pg_dump utility then by default it takes the dump in plain text SQL file irrespective of the format you are using in the dump file name. So when you try to restore or rebuild your database from that dump, it would think it is a plain text SQL file and hence it will ask to restore using psql utility as pg_restore won’t able to detect the magic number to restore. So basically to solve this problem, you need to restore simply by running psql -f cyberithub.dump command as shown below.

postgres@ubuntu:~$ psql -f cyberithub.dump
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET

You can also specify the username by using -U <user_name> with psql command as shown below.

postgres@ubuntu:~$ psql -U postgres -f cyberithub.dump
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET

Solution 2: Use pg_dump with correct format

Another solution that you can follow to fix this problem is that you can retake the dump by specifying correct option with pg_dump utility. For example if you need to take the dump in .dump format then you can use -F c as shown below.

postgres@ubuntu:~$ pg_dump -F c cyberithub_db > cyberithub_db.dump

Similarly, if you would like to take dump in .tar format then you need to use -F t as shown below.

postgres@ubuntu:~$ pg_dump -F t cyberithub_db > cyberithub_db.tar

Now if you try to restore or rebuild the database from the dump you took into an existing database called cyberithub_db then it should work fine as expected. For example, in my case when I tried to restore from cyberithub_db.dump into cyberithub_db database using pg_restore utility then it worked fine as expected.

postgres@ubuntu:~$ pg_restore -d cyberithub_db cyberithub_db.dump

If in case, you don’t have a database to restore or would like to recreate the database from dump then you need to use -C option as well with above pg_restore command. So now the command will look like below.

postgres@ubuntu:~$ pg_restore -C -d cyberithub_db cyberithub_db.dump

Similarly, if you try to restore the database from .tar dump into cyberithub_db database then you need to run like below. It should work fine without showing any error.

postgres@ubuntu:~$ pg_restore -d cyberithub_db cyberithub_db.tar

To recreate database from dump, just like as shown above, here also you need to add -C option with pg_restore command as shown below.

postgres@ubuntu:~$ pg_restore -C -d cyberithub_db cyberithub_db.tar

Hope above solution would be enough to solve your "pg_restore: error: input file appears to be a text format dump. Please use psql" as well. Please let me know for any feedback or suggestions in the comment box.