I like “ but be prepared for a very very very long restore time... very very very long as in you don't want this”
Sent from Yahoo Mail for iPhone On Friday, February 19, 2021, 3:58 PM, Guillaume Lelarge <guilla...@lelarge.info> wrote: Hi, Le ven. 19 févr. 2021 à 21:08, James B. Byrne <byrn...@harte-lyne.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.
