Debian : No echo after pg_dump | psql

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Debian : No echo after pg_dump | psql

Moreno Andreo
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.-



Reply | Threaded
Open this post in threaded view
|

Re: Debian : No echo after pg_dump | psql

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Debian : No echo after pg_dump | psql

Andrew Gierth
In reply to this post by Moreno Andreo
>>>>> "Moreno" == Moreno Andreo <[hidden email]> 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)

Reply | Threaded
Open this post in threaded view
|

Re: Debian : No echo after pg_dump | psql

Moreno Andreo
Adrian, Andrew, thanks and apologies for the late reply

Il 30/11/2018 05:08, Andrew Gierth ha scritto:

>>>>>> "Moreno" == Moreno Andreo <[hidden email]> 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.-



Reply | Threaded
Open this post in threaded view
|

Re: Debian : No echo after pg_dump | psql

Adrian Klaver-4
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 <[hidden email]> 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
[hidden email]