Re: db restore from sql file

On Fri, Feb 19, 2021 at 8:00 PM James B. Byrne <byrnejb(at)harte-lyne(dot)ca>
wrote:

> I used pgadmin4 to create a dump file in plain sql.
>
> I am trying to use this file to populate a test database on another
> postgresql
> host. I can find the file previously created. It appears correct:
>
> [root(at)vhost01 ~ (master)]# file
> /var/db/pgadmin4/storage/admin_harte-lyne.ca/redmine_backup_locate.sql
> /var/db/pgadmin4/storage/admin_harte-lyne.ca/redmine_backup_locate.sql
> <http://admin_harte-lyne.ca/redmine_backup_locate.sql/var/db/pgadmin4/storage/admin_harte-lyne.ca/redmine_backup_locate.sql>:
> ASCII
> text, with very long lines
>
> [root(at)vhost01 ~ (master)]# more
> /var/db/pgadmin4/storage/admin_harte-lyne.ca/redmine_backup_locate.sql
> --
> -- PostgreSQL database dump
> --
>
> -- Dumped from database version 9.6.17
> -- Dumped by pg_dump version 12.5
>
> -- Started on 2021-02-19 14:32:31 EST
>
> SET statement_timeout = 0;
> SET lock_timeout = 0;
> . . .
>
> The target database exists on the target host. However, when I try to run
> the
> restore function in pgadmin4 I get this error:
>
> /usr/local/bin/pg_restore --host "
> pgsql-dbms.internal.hamilton.harte-lyne.ca"
> --port "5432" --username "postgres" --no-password --role "postgres"
> --dbname
> "hll_redmine_copy" --verbose
> "/var/db/pgadmin4/storage/admin_harte-lyne.ca/redmine_backup_locate.sql
>
> pg_restore: error: input file appears to be a text format dump. Please use
> psql
>
>
>
> Is there no way to restore from an sql file from within pgadmin4?
>

Not at present, because pg_restore can't do it. You'd need to feed the file
into the psql command line utility.

Better yet, use the 'custom' format when you're dumping the data, as it
allows for a lot more flexibility when loading. Plain dumps are normally
only preferred if you want to go and manually edit them in a text editor.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com