Re: db restore from sql file

Hi,

Le ven. 19 févr. 2021 à 21:08, James B. Byrne <byrnejb(at)harte-lyne(dot)ca> a
écrit :

>
>
> On Fri, February 19, 2021 15:04, James B. Byrne wrote:
> >
> >
> > On Fri, February 19, 2021 15:00, James B. Byrne wrote:
> >>
> >>
> >> Is there no way to restore from an sql file from within pgadmin4?
> >>
>

As far as I can tell, no.

>>
> >
> > I can load it as an SQL query of course. And I tried that. But the
> dump file
> > contains this line which causes the query to fail:
> >
> > ERROR: syntax error at or near "\"
> > LINE 32: \connect hll_redmine_copy
> > ^
> > SQL state: 42601
> > Character: 845
> >
> >
> > I have removed this and will try again. But the existence of that line
> in an
> > SQL dump is a bit disconcerting.
> >
>

Well, I guess the \connect is due to the fact that you asked that the dump
contains a CREATE DATABASE statement. This is what happens when you use the
-C / --createcommand line option of pg_dump. With such a statement, pg_dump
has to issue a \connect meta-command after the CREATE DATABASE so that psql
connects to the newly created database in order to restore the rest of the
dump.

> No, it died here:
>
> COPY public.attachments (id, container_id, container_type, filename,
> disk_filename, filesize, content_type, digest, downloads, author_id,
> created_on, description, disk_directory) FROM stdin;
> 1 8 Issue putty.jpg 080513110337_putty.jpg 64088
> image/jpeg 7138a6b00422eff6a9c4cee64dc79f00 0 3
> 2006-03-16
> 19:16:09 \N \N
>
> ERROR: syntax error at or near "1"
> LINE 2685: 1 8 Issue putty.jpg 080513110337_putty.jpg 64088 image/jpeg ...
> ^
> SQL state: 42601
> Character: 72007
>
>
That's the issue that makes me say you can't use pgAdmin's query tool to
restore a plain/SQL format dump, unless you also ask to replace COPY
statements with INSERT statements (but be prepared for a very very very
long restore time... very very very long as in you don't want this).

A plain/SQL dump is to be restored with psql.

--
Guillaume.