Обсуждение: Debian : No echo after pg_dump | psql
Hi guys, I'm facing a strange thing on my test server (Google Cloud) On my Debian 9 box I'm running Postgres 9.6.10, and I'm transferring some databases from another server (Debian 8, PG 9.5.15). The command I'm using is root@xxxxx:~# pg_dump -v -C -h <remote host> -p 6543 -U postgres <dbname> | psql -h localhost -p 6543 -U postgres <dbname> It presents a double password prompt after I run it: Password: Password for user postgres: I enter _once_ my postgres password, and it starts processing data. At a certain point it suddenly stops processing without prompting anything, and goes on only if I insert my postgres password again, as if it was for one of the two prompts it showed at the beginning where I entered only one password. After another bit of processing, it asks another time (the third!) my postgres password. After I entered it, it goes to the end (and there are no issues in data or anything simia. After this, I have no more echo on what I'm typing, but if I type something and press enter, I can see the result of the command I just typed (and have not seen) on the console. Restarting the SSH session resolves the problem. The same strange behavior is the same if I switch servers (running pg_dump on Debian 8/PG 9.5 connecting on Debian 9/PG 9.6) Another thing I was forgetting to report: If I create database (just database, not schema) on target machine, I receive the error "database xxxx already exists" but the dump goes on If I don't create it, I receive the error "database xxxx does not exist" and processing aborts. Hope I've been clear enough...... Has someone bumped into it? Thanks in advance Moreno.-
On 11/29/18 8:12 AM, Moreno Andreo wrote: > Hi guys, > I'm facing a strange thing on my test server (Google Cloud) > > On my Debian 9 box I'm running Postgres 9.6.10, and I'm transferring > some databases from another server (Debian 8, PG 9.5.15). > > The command I'm using is > root@xxxxx:~# pg_dump -v -C -h <remote host> -p 6543 -U postgres > <dbname> | psql -h localhost -p 6543 -U postgres <dbname> > It presents a double password prompt after I run it: > Password: Password for user postgres: > > I enter _once_ my postgres password, and it starts processing data. > > At a certain point it suddenly stops processing without prompting > anything, and goes on only if I insert my postgres password again, as if > it was for one of the two prompts it showed at the beginning where I > entered only one password. > After another bit of processing, it asks another time (the third!) my > postgres password. After I entered it, it goes to the end (and there are > no issues in data or anything simia. > > After this, I have no more echo on what I'm typing, but if I type > something and press enter, I can see the result of the command I just > typed (and have not seen) on the console. > > Restarting the SSH session resolves the problem. > > The same strange behavior is the same if I switch servers (running > pg_dump on Debian 8/PG 9.5 connecting on Debian 9/PG 9.6) Look into the .pgpass file: https://www.postgresql.org/docs/10/libpq-pgpass.html Or if you already have one check that is set up for both the remote and local servers. > > Another thing I was forgetting to report: > > If I create database (just database, not schema) on target machine, I > receive the error "database xxxx already exists" but the dump goes on Might look at --clean: https://www.postgresql.org/docs/10/app-pgdump.html -c --clean Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.) This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore. > If I don't create it, I receive the error "database xxxx does not exist" > and processing aborts. > > Hope I've been clear enough...... > > Has someone bumped into it? > > Thanks in advance > > Moreno.- > > > > -- Adrian Klaver adrian.klaver@aklaver.com
>>>>> "Moreno" == Moreno Andreo <moreno.andreo@evolu-s.it> writes: Moreno> The command I'm using is Moreno> root@xxxxx:~# pg_dump -v -C -h <remote host> -p 6543 -U postgres Moreno> <dbname> | psql -h localhost -p 6543 -U postgres <dbname> Moreno> It presents a double password prompt after I run it: Moreno> Password: Password for user postgres: This is going to prompt once for the remote host's password and twice for the local one (because -C), and the concurrently-running commands are going to be fighting over access to the terminal to do it. Best avoided by using pgpass or non-password-based auth methods. More seriously, you're misunderstanding how -C works. When you use -C, the database you specify to psql (or pg_restore) is NOT the database you're restoring into - the restored db will ALWAYS have the same name as it had when dumped (if that's not what you want then don't use -C). Instead, the database you specify to psql or pg_restore is the database to connect to to issue the CREATE DATABASE command, which should usually be 'postgres'. That explains this bit: Moreno> If I create database (just database, not schema) on target Moreno> machine, I receive the error "database xxxx already exists" but Moreno> the dump goes on If I don't create it, I receive the error Moreno> "database xxxx does not exist" and processing aborts. -- Andrew (irc:RhodiumToad)
Adrian, Andrew, thanks and apologies for the late reply Il 30/11/2018 05:08, Andrew Gierth ha scritto: >>>>>> "Moreno" == Moreno Andreo <moreno.andreo@evolu-s.it> writes: > Moreno> The command I'm using is > Moreno> root@xxxxx:~# pg_dump -v -C -h <remote host> -p 6543 -U postgres > Moreno> <dbname> | psql -h localhost -p 6543 -U postgres <dbname> > Moreno> It presents a double password prompt after I run it: > Moreno> Password: Password for user postgres: > > This is going to prompt once for the remote host's password and twice > for the local one (because -C), and the concurrently-running commands > are going to be fighting over access to the terminal to do it. Best > avoided by using pgpass or non-password-based auth methods. > > More seriously, you're misunderstanding how -C works. When you use -C, > the database you specify to psql (or pg_restore) is NOT the database > you're restoring into - the restored db will ALWAYS have the same name > as it had when dumped (if that's not what you want then don't use -C). > Instead, the database you specify to psql or pg_restore is the database > to connect to to issue the CREATE DATABASE command, which should usually > be 'postgres'. That explains this bit: > > Moreno> If I create database (just database, not schema) on target > Moreno> machine, I receive the error "database xxxx already exists" but > Moreno> the dump goes on If I don't create it, I receive the error > Moreno> "database xxxx does not exist" and processing aborts. > I removed -C usage since the target database (the database itself, not the schema) is created with a CREATE DATABASE before issuing pg_dump (so I don't need it), but strange prompt behavior remained the same I'm having a really hard time these days, so I can't investigate further. Maybe on holidays, when I hope the pressure will be released a bit. Will report as soon as I can. THanks again Cheers, Moreno.-
On 12/12/18 4:51 AM, Moreno Andreo wrote: > Adrian, Andrew, thanks and apologies for the late reply > > Il 30/11/2018 05:08, Andrew Gierth ha scritto: >>>>>>> "Moreno" == Moreno Andreo <moreno.andreo@evolu-s.it> writes: >> Moreno> The command I'm using is >> Moreno> root@xxxxx:~# pg_dump -v -C -h <remote host> -p 6543 -U >> postgres >> Moreno> <dbname> | psql -h localhost -p 6543 -U postgres <dbname> >> Moreno> It presents a double password prompt after I run it: >> Moreno> Password: Password for user postgres: >> >> This is going to prompt once for the remote host's password and twice >> for the local one (because -C), and the concurrently-running commands >> are going to be fighting over access to the terminal to do it. Best >> avoided by using pgpass or non-password-based auth methods. >> >> More seriously, you're misunderstanding how -C works. When you use -C, >> the database you specify to psql (or pg_restore) is NOT the database >> you're restoring into - the restored db will ALWAYS have the same name >> as it had when dumped (if that's not what you want then don't use -C). >> Instead, the database you specify to psql or pg_restore is the database >> to connect to to issue the CREATE DATABASE command, which should usually >> be 'postgres'. That explains this bit: >> >> Moreno> If I create database (just database, not schema) on target >> Moreno> machine, I receive the error "database xxxx already exists" but >> Moreno> the dump goes on If I don't create it, I receive the error >> Moreno> "database xxxx does not exist" and processing aborts. >> > I removed -C usage since the target database (the database itself, not > the schema) is created with a CREATE DATABASE before issuing pg_dump (so > I don't need it), but strange prompt behavior remained the same > > I'm having a really hard time these days, so I can't investigate > further. Maybe on holidays, when I hope the pressure will be released a > bit. Understood. > > Will report as soon as I can. > > THanks again > > Cheers, > > Moreno.- > > > > -- Adrian Klaver adrian.klaver@aklaver.com