Error on restore into doltgres (pg_restore)

[VikramChennai]

Error restoring database: pg_restore: connecting to database for restore
pg_restore: error: query failed: ERROR: function: 'set_config' not found (errno 1105) (sqlstate HY000)
pg_restore: detail: Query was: SELECT pg_catalog.set_config('search_path', '', false);

What i was running:

pg_restore_command = [
PG_RESTORE_PATH,
f"--dbname={connection_uri}",
"--no-owner",
"--no-privileges",
"--clean",
"--if-exists",
"-v",
dump_file_path
]

[zachmu]

Hi Vikram,

Getting pg_dump restores working is an ongoing piece of work for us. We can implement the missing set_config function pretty easily, but it's likely there would be additional problems with your restore command after that. But, we could probably knock them all out for you at once if you'd be willing to share your dump file with us.

Zach

[fulghum]

Thanks for sharing the dump file with us via Discord. I've been digging in and found a few interesting things already...

One callout is that Doltgres does not support user defined functions yet, but it's on our radar and something we want to get to soon. Here's the tracking issue for that work: #756

I've found a few issues with identifier quoting – I've patched the nextval function and the COPY statement locally to fix that, and have been able to get a bit farther with the data loading. I'll get those fixes cleaned up, add some tests, and open PRs for those.

I'll keep digging in and let you know what else we find here.

[fulghum]

A couple more updates on this one...

#852 is the tracking issue for set_config() support.

I just merged in #851, which contains a couple small fixes for double-quoted identifier parsing that your SQL dump needs.

I started digging into another issue I identified, but I don't have a fix yet. I opened #850 to track that work and will keep digging in deeper there.

I also noticed that the dump file contains CREATE PUBLICATION and ALTER PUBLICATION statements. Doltgres doesn't support publications currently, and my understanding is that this feature is only used for replication. My guess is that your app probably doesn't need these on Doltgres, but let us know if you do need publication support for any reason and we'll be happy to open a tracking issue for it and start scoping the work.

[fulghum]

We've resolved the separate tracking issues we cut from this issue (other than user defined function support), so I'm going to go ahead and close this one out. Thank you for the feedback and for helping us find several places to improve Doltgres.

Feel free to comment or create new issues with any other snags you hit and we'll be happy to dig in and address them.