Horton

Thread

  1. pg_dump backup problems with password authentication

    Ricardo Dias Marques <ricmarques@spamcop.net> — 2000-12-07T13:07:17Z

    Hi all,


I am using PostgreSQL 7.0.2 in RedHat Linux 6.2

I have changed the default authentication, in the pg_hba.conf
file, from trust to password.

Unfortunately, this is giving me problems with pg_dump

I am trying to do a copy of just one database (with all its
tables, primary and foreign keys, check clauses, triggers, etc).

So, I followed the instructions at:
http://postgresql.rmplc.co.uk/users-lounge/docs/7.0/admin/manage-ag1655.htm

and did a pg_dump dbname > dbname.pgdump
(I replaced dbname by my database name, obviously)


Because I have password authentication set, I get an 
error message:

Connection to database 'dbname' failed.
fe_sendauth: no password supplied

So, I did a man pg_dump and found out that -u would prompt
for username and password, and -v would give a verbose output.
And so I did:
pg_dump -u -v dbname > dbname.pgdump


The problem is when I press the ENTER key on that command:
it just sits there! Eventually, I have to hit CTRL+C to get out.


Can any kind soul explain me what I am doing wrong?  :(


Thanks in advance,
Ricardo Dias Marques
ricmarques@spamcop.net

  2. Re: pg_dump backup problems with password authentication

    Richard Poole <richard.poole@vi.net> — 2000-12-07T14:21:41Z

    On Thu, Dec 07, 2000 at 01:07:17PM +0000, Ricardo Dias Marques wrote:

> So, I did a man pg_dump and found out that -u would prompt
> for username and password, and -v would give a verbose output.
> And so I did:
> pg_dump -u -v dbname > dbname.pgdump
> 
> 
> The problem is when I press the ENTER key on that command:
> it just sits there! Eventually, I have to hit CTRL+C to get out.
> 
> 
> Can any kind soul explain me what I am doing wrong?  :(

Unfortunately, the prompts for your username and password come out
on standard output, which you're redirecting to the file. So you
don't see them. But pg_dump is listening; type in your username and
password as if you *had* seen the prompts and it will all work.
Alternatively, you can use "echo" to give them:

echo -n -e "username\npassword\n" | pg_dump -u -v dbname > dbname.pgdump

This is insecure, as your password is briefly visible in a full ps output,
but it works non-interactively, so you can do it in scripts (e.g., from
cron). Of course, that's potentially even more insecure...

Richard

  3. Re: [ADMIN] pg_dump backup problems with password authentication

    Trurl McByte <trurl@anything3d.com> — 2000-12-07T14:42:21Z

    -----BEGIN PGP SIGNED MESSAGE-----

Hi!

============
trurl=# select '["2000-11-01 10:00:00+02" "2000-11-30 10:00:00+02"]'::tinterval =
trurl=# '["2000-09-01 10:00:00+03" "2000-09-30 10:00:00+03"]'::tinterval;
 ?column?
- ----------
 t
(1 row)
============
I'm crazy?

P.S. PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2



- -- 
      Trurl McByte, Capt. of StasisCruiser "Prince"
|InterNIC: AR3200                   RIPE: AR1627-RIPE|
|--98 C3 78 8E 90 E3 01 35  87 1F 3F EF FD 6D 84 B3--|

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 5.0i for non-commercial use
Charset: noconv

iQCVAwUBOi+h0CC2QvVu8HIfAQF4hQP/RlPcwtl9ZE1hG9VT9itEB4Sys72Hyik8
Wcn+xo2+Rsl+fT83j9HccB/w/oZyzrKUWQIVC6h9ORU6CFmytD8NWZ28Uta3sQh/
U0YRmPSO860z+ObLSvTiLFexhQGNuwA1A/LSfYovqTfLTS6Lq2E7qNXoP92raebg
yIbeednitf8=
=RG4K
-----END PGP SIGNATURE-----

  4. Re: Re: [ADMIN] pg_dump backup problems with password authentication

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-07T15:59:17Z

    Trurl McByte <trurl@anything3d.com> writes:
> trurl=# select '["2000-11-01 10:00:00+02" "2000-11-30 10:00:00+02"]'::tinterval =
> trurl=# '["2000-09-01 10:00:00+03" "2000-09-30 10:00:00+03"]'::tinterval;
>  ?column?
> - ----------
>  t
> (1 row)

The tinterval comparison functions consider two tintervals equal if they
have the same length (duration).  Whether this is reasonable I dunno,
but that's how that datatype has always worked.

			regards, tom lane

  5. Re: pg_dump backup problems with password authentication

    Ricardo Dias Marques <ricmarques@spamcop.net> — 2000-12-07T17:51:59Z

    Hi again,

I would like to thank Vasileiadis Spyros, Michael Ansley and 
Richard Poole who all pointed out the nature of the problem 
and the correct solution.


SUMMARY

The original situation was this:
1) I had changed authentication in pg_hba.conf from trust to 
password
2) This gave me a problem with pg_dump  
When I ran the command:
pg_dump -D -u -v databasename > database.sql
I didnt't get an Username prompt (the -u switch would do that)

The explanation for this was that the Username prompt appears
in stdout (STanDard OUTput), which *usually* is the screen,
but because I was redirecting output to a file, the Username:
prompt was being saved in the output file (database.sql)

The solution was to write the pg_dump command above, press
ENTER, and then write the username (the Username prompt doesn't
appear, as explained above), press ENTER, write the password
and press ENTER again. Using the -v switch, some lines appear
which show the operations being done (e.g., "reading 
user-defined types", "finding Triggers for relation:", etc)

To restore the data, I edited the database.sql with joe 
(I found out that my default configuration of pico was
breaking the lines at wrap points). I removed the Username,
and Password lines, and the \connect - postgres line. Then
I wrote the password in the first line (I didn't need to
write the Username, because I was logged in as user
postgres)

Then, I created the new database:
createdb newdatabase

And typed the restore command:
psql newdatabase < database.sql

Et voilá!  :)


Thanks for all the help, and I hope this summary will be
useful to other people with the same problem (let me live
in my illusion that I'm not the only one, OK?  ;-)


Best wishes,
Ricardo Dias Marques
ricmarques@spamcop.net

  6. Re: Re: [ADMIN] pg_dump backup problems with password authentication

    Trurl McByte <trurl@anything3d.com> — 2000-12-08T07:57:43Z

    On Thu, 7 Dec 2000 (Yesterday), Tom Lane wrote:

  TL> Trurl McByte <trurl@anything3d.com> writes:
  TL> > trurl=# select '["2000-11-01 10:00:00+02" "2000-11-30 10:00:00+02"]'::tinterval =
  TL> > trurl=# '["2000-09-01 10:00:00+03" "2000-09-30 10:00:00+03"]'::tinterval;
  TL> >  ?column?
  TL> > - ----------
  TL> >  t
  TL> > (1 row)
  TL> 
  TL> The tinterval comparison functions consider two tintervals equal if they
  TL> have the same length (duration).  Whether this is reasonable I dunno,
  TL> but that's how that datatype has always worked.
Ok. But in next sample I have system crash:
=============
trurl=# create table ttable ( id int4, tt tinterval );
CREATE
trurl=# insert into ttable VALUES ( 1, '["2000-11-01 10:00:00" "2000-12-01 10:00:00"]');
INSERT 327690 1
trurl=# insert into ttable VALUES ( 2 );
INSERT 327691 1
trurl=# select * from ttable where tt = '["2000-11-01 10:00:00" "2000-12-01 10:00:00"]'::tinterval;
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#
=============
NULL value in "tinterval"... :(


  TL> 
  TL> 			regards, tom lane
  TL> 

-- 
      Trurl McByte, Capt. of StasisCruiser "Prince"
|InterNIC: AR3200                   RIPE: AR1627-RIPE|
|--98 C3 78 8E 90 E3 01 35  87 1F 3F EF FD 6D 84 B3--|