Restore 'fails' with: pg_restore: error: could not execute query: ERROR: schema "public" already exists

Virtualmin
1

This appears to be happening even if the Postgres DB for that account is empty.

Original account was in a server with PG9 (but get the same message on a server with PG13 as well as one with PG15).

Here’s the full output:

virtualmin restore-domain --source /home/for-restore/SITE.com.tar.gz --all-features --domain SITE.com
Checking for missing features ..
.. all features in backup are supported

Checking for errors in backup ..
.. no errors found

Starting restore..
Extracting backup archive file ..
.. done

Re-creating virtual server SITE.com ..
    Creating administration group SITE.com ..
    .. done

    Creating administration user SITE.com ..
    .. done

    Creating aliases for administration user ..
    .. done

    Adding administration user to groups ..
    .. done

    Creating home directory ..
    .. done

    Creating mailbox for administration user ..
    .. done

    Adding new DNS zone ..
    .. done

    Adding to email domains list ..
    .. done

    Adding new virtual website ..
    .. done

    Adding webserver user apache to server's group ..
    .. done

    Performing other Apache configuration ..
    .. done

    Setting up log file rotation ..
    .. done

    Creating PostgreSQL login ..
    .. done

    Creating PostgreSQL database SITE_com ..
    .. done

    Saving server details ..
    .. done

    Re-starting DNS server ..
    .. done

    Applying web server configuration ..
    .. done

Restoring backup for virtual server SITE.com ..
    Restoring virtual server password, quota and other details ..
    .. done

    Updating administration password and quotas ..
    .. done

    Restoring Cron jobs ..
    .. done

    Extracting TAR file of home directory ..
    .. done

    Setting ownership of home directory ..
    .. done

    Re-creating records in DNS domain ..
    .. done

    Restoring Apache virtual host configuration ..
    .. done

    Checking restored PHP execution mode ..
sh: lsof: command not found
    .. mode Apache mod_php cannot be used, switching to FPM. 

    Restoring Apache log files ..
    .. done

    Restoring Logrotate configuration ..
    .. done

    Re-loading PostgreSQL database SITE_com ..
    .. load failed! pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 6; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;


pg_restore: warning: errors ignored on restore: 1


Re-starting DNS server ..
.. done

Restarting PHP-FPM server ..
.. failed : Job for php-fpm.service failed because the control process exited with error code. See "systemctl status php-fpm.service" and "journalctl -xe" for details.


Applying web server configuration ..
.. done

Restore failed!

Looking at the last sentence of this response it appears to be because it is being specified. Is Virtualmin doing this on restore? Does the restore script need to be updated? (It appears that the PG database does get restored, but the Restore failed! message might worry some.)

The public schema is a bit of a strange beast, and pg_dump has to
special-case it in some ways. We’ve moved those special cases around
from time to time, too. So one likely explanation here has to do
with version discrepancies between the pg_dump that made the dump
file and the pg_restore that’s restoring it.

Also, I do not think that you’re telling us the whole truth about
how your colleague is running pg_dump and/or pg_restore. There
> shouldn’t be any “CREATE SCHEMA public” issued if you didn’t say

2

So it looks like the restores were not successful - users (under Virtualmin > domain > Edit Users) were not restored

And then when I go to recreate manually it says:

Failed to save user : Home directory /home/domain.com/homes/contact already exists

3

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.

4

What OS and version are you seeing this issue on? Also, since the last comment was over a year ago, do you still have this issue with the latest Virtualmin 7.10.0 and Webmin 2.111?

6

Sorry for delay in responding. For context in case anyone finds this thread in future, this bit was requested to be added to my post above:

/Edit/
After getting the same again on another server it looks like the error can be ignored, as the domain/account gets restored as usual

Yes, I got the same error importing into a new install (so current version at that time) though the import worked (apart from giving the error). However the backup was from an older version (webmin 1.973 and virtualmin 6.16).

(I no longer have that server so may not be able to carry out any other tests sorry Ilia)