Re: db restore from sql file

[James B. Byrne]

On Mon, February 22, 2021 04:14, Dave Page wrote:
> On Fri, Feb 19, 2021 at 8:00 PM James B. Byrne 
> wrote:
>
>>
>> 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.

Thanks.  I did that to get past the immediate difficulty.  But it is
inconvenient to unload from one host, transfer the dump to another host, log on
to the second host, and then use psql.  As compared to using pgadim where
everything is handled via one interface on a single host.

Regards,


-- 
***  e-Mail is NOT a SECURE channel  ***
Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3

Re: db restore from sql file

[Dave Page]

On Fri, Feb 19, 2021 at 8:00 PM James B. Byrne 
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@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
> :
> ASCII
> text, with very long lines
>
> [root@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

Re: db restore from sql file

[Endre Pekarik]

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 
 wrote:

Hi,
Le ven. 19 févr. 2021 à 21:08, James B. Byrne  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.

Re: db restore from sql file

[Guillaume Lelarge]

Hi,

Le ven. 19 févr. 2021 à 21:08, James B. Byrne  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  7138a6b00422eff6a9c4cee64dc79f000   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.

Re: db restore from sql file

[James B. Byrne]

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?
>>
>>
>
> 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.
>

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  7138a6b00422eff6a9c4cee64dc79f000   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

-- 
***  e-Mail is NOT a SECURE channel  ***
Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3

Re: db restore from sql file

[James B. Byrne]

On Fri, February 19, 2021 15:00, James B. Byrne 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@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: ASCII
> text, with very long lines
>
> [root@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?
>
>

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.

-- 
***  e-Mail is NOT a SECURE channel  ***
Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3