Dump/restore doesn't work (for versions 1.2.3, 1.2.4 only dump works)!

[borodaev]

Hello!
Looks like #197 is not finished. For versions 1,2,3 and 1.2.4 (I use pg14 on win 10) I can dump DB with patches but I can't restore
How do you guys live with it?
During restore pcpatch type has a 'lazy', delayed dependency to public.pointcloud_formats table records (to pcid field of the records). This indirect dependency causes error for copy commands execution during pg_restore because pointcloud_formats can be empty at the time when copy is executing.
Sometime restore can work fine if jobs are used and pointcloud_formats gets filled before patches which use corresponding format in this table. For example with attached dump file restore error can be reproduced if no jobs used but for jobs==3 it works fine. So the problem with restore is not related to how DB was created (it can be created with new pgpc version installed with old pgpc version and upgraded then using ALTER EXTENSION)

So I found only one workaround to restore db with patches:
this works only if there are no custom types/functions/tables in public schema:

1. create new db test

2. add exts

CREATE EXTENSION postgis;
CREATE EXTENSION pointcloud;
CREATE EXTENSION pointcloud_postgis;

3. dump data from public only schema
   pg_dump.exe --file "C:\temp\d_public_d.dmp" --host "localhost" --port "5432" --username "postgres" --verbose --format=c --blobs --data-only --schema=public "demolog"
4. dump all from non-public only schema
   pg_dump.exe --file "C:\temp\d_n_public.dmp" --host "localhost" --port "5432" --username "postgres" --verbose --format=c --blobs --exclude-schema=public "demolog"
5. restore public data
   pg_restore.exe --host "localhost" --port "5432" --username "postgres" --dbname "test" --data-only --verbose "C:\temp\d_public_d.dmp"
6. restore the rest
   pg_restore.exe --host "localhost" --port "5432" --username "postgres" --dbname "test" --verbose "C:\temp\d_n_public.dmp"
   db_broken.zip

update
it is possible to restore even with custom data in public - you just need to create full dump and then restore public only schema and then - the rest:
restore.cmd:

@echo off
SET DIR=%cd%
if "%5"=="" (
    echo Arguments: host user password db_name backup_file
	set errorlevel=1
	goto err
)
SET PGPASSWORD=%3
@echo on
cd /d C:\Program Files\PostgreSQL\14\bin
call psql -U %2 -d postgres -c "DROP DATABASE IF EXISTS %4;"
@echo off
if errorlevel 1 (
   goto err
)
@echo on
psql -U %2 -d postgres -c "CREATE DATABASE %4;"
@echo off
if errorlevel 1 (
   goto err
)
@echo on
psql -U %2 -d %4 -c "CREATE EXTENSION postgis; CREATE EXTENSION pointcloud; CREATE EXTENSION pointcloud_postgis;"
@echo off
if errorlevel 1 (
   goto err
)
@echo on
pg_restore.exe --host %1 --port "5432" --username %2 --dbname %4 --schema=public --verbose %5
@echo off
if errorlevel 1 (
   goto err
)
@echo on
pg_restore.exe --host %1 --port "5432" --username %2 --dbname %4 --exclude-schema=public --verbose %5
@echo off
if errorlevel 1 (
   goto err
)
@echo on
echo Success
cd /d %DIR%
Exit /B 0
:err
cd /d %DIR%
Exit /B %errorlevel%

usage example:
restore localhost postgres sa test_db "c:\temp\test.dmp"

env:
SELECT version(), pc_version(), PostGIS_Version()
"PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit" "1.2.4" "3.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"

[borodaev]

Updated description with new (more convenient) workaround

[borodaev]

I will try to reproduce it on ubuntu

[borodaev]

I've reproduced it on ubuntu as well (tried pg_restore from windows and from ubuntu, also tried from dbeaver)
SELECT version(), pc_version(), PostGIS_Version()
"PostgreSQL 14.6 (Ubuntu 14.6-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit"
"1.2.4"
"3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
So now I'm sure that is not possible to simply restore db with patches on windows and unix
How it is possible? How do you live with it?

[autra]

@borodaev it works with pg13 for me. Maybe a regression with pg14? I'll try to test if I have some time.

[borodaev]

@autra, did you set number of jobs > 1 in restore settings? I will also try on pg13

[autra]

did you set number of jobs > 1 in restore settings?

@borodaev I can't remember, sorry :-/