Restore

On Friday 24 December 2010 12:36:32 pm Bob Pawley wrote:

Hi

I am attempting to restore a database using -

psql PDW < PDW_June_10.sql

psql –U postgres PDW < PDW_June_10.sql

psql –U postgres -d PDW -f PDW_June_10.sql

The response asks me for a password.

I use the same password with which I connect to the server but it is not
accepted.

Without the –U postgres identifier it asks me for the password of my
computer - which doesn’t exist.

Without a specified -U it psql will use your system user name.
See here for all the gory details:
http://www.postgresql.org/docs/9.0/interactive/app-psql.html

How can I get around this??

Bob

--
Adrian Klaver
adrian.klaver@gmail.com

Thanks Adrian but

psql –U postgres -d PDW -f PDW_June_10.sql

asks - "Password for user postgres:"

When I type the password the cursor doesn't respond and on enter I get
password failed.

Bob

-----Original Message-----
From: Adrian Klaver
Sent: Friday, December 24, 2010 2:59 PM
To: pgsql-general@postgresql.org
Cc: Bob Pawley
Subject: Re: [GENERAL] Restore

On Friday 24 December 2010 12:36:32 pm Bob Pawley wrote:

Hi

I am attempting to restore a database using -

psql PDW < PDW_June_10.sql

psql –U postgres PDW < PDW_June_10.sql

psql –U postgres -d PDW -f PDW_June_10.sql

The response asks me for a password.

I use the same password with which I connect to the server but it is not
accepted.

Without the –U postgres identifier it asks me for the password of my
computer - which doesn’t exist.

Without a specified -U it psql will use your system user name.
See here for all the gory details:
http://www.postgresql.org/docs/9.0/interactive/app-psql.html

How can I get around this??

Bob

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Friday 24 December 2010 3:28:38 pm Bob Pawley wrote:

Thanks Adrian but

psql –U postgres -d PDW -f PDW_June_10.sql

asks - "Password for user postgres:"

When I type the password the cursor doesn't respond and on enter I get
password failed.

Bob

A little bit of testing on my part showed that your form of connecting should
work also i.e.psql –U postgres PDW < PDW_June_10.sql.
A couple of questions.
Can you connect to a database using psql and -U postgres?
Does the postgres user have a password?
If you can connect to a database with psql what does \l show? What I am looking
for is whether PDW has its case preserved or not?

--
Adrian Klaver
adrian.klaver@gmail.com

On Fri, Dec 24, 2010 at 3:38 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On Friday 24 December 2010 3:28:38 pm Bob Pawley wrote:

Thanks Adrian but

psql –U postgres -d PDW  -f PDW_June_10.sql

asks - "Password for user postgres:"

When I type the password the cursor doesn't respond and on enter I get
password failed.

Bob

A little bit of testing on my part showed that your form of connecting should
work also i.e.psql –U postgres PDW < PDW_June_10.sql.
A couple of questions.
Can you connect to a database using psql and -U postgres?
Does the postgres user have a password?
If you can connect to a database with  psql what does \l show? What I am looking
for is whether PDW has its case preserved or not?

How about the pg_hba.conf setting? Is it set to something like md5?

I have tried this a number of times -
psql –U postgres PDW < PDW_June_10.sql. (with and without spaces between U &
postgres and/or < and PDW_June)

Sometimes I am asked for a postgres password, once I was asked for the PDW
password (this makes sense and which I did enter).

When I entered the password it either said password failed or it simply went
back to the root command.

No database information was sent.

pg_hba.conf sets method as md5

I don't know what is meant by your reference to \1.

Bob

-----Original Message-----
From: bricklen
Sent: Friday, December 24, 2010 3:47 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore

On Fri, Dec 24, 2010 at 3:38 PM, Adrian Klaver <adrian.klaver@gmail.com>
wrote:

On Friday 24 December 2010 3:28:38 pm Bob Pawley wrote:

Thanks Adrian but

psql –U postgres -d PDW -f PDW_June_10.sql

asks - "Password for user postgres:"

When I type the password the cursor doesn't respond and on enter I get
password failed.

Bob

A little bit of testing on my part showed that your form of connecting
should
work also i.e.psql –U postgres PDW < PDW_June_10.sql.
A couple of questions.
Can you connect to a database using psql and -U postgres?
Does the postgres user have a password?
If you can connect to a database with psql what does \l show? What I am
looking
for is whether PDW has its case preserved or not?

How about the pg_hba.conf setting? Is it set to something like md5?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Friday 24 December 2010 4:03:52 pm Bob Pawley wrote:

I have tried this a number of times -
psql –U postgres PDW < PDW_June_10.sql. (with and without spaces between U
& postgres and/or < and PDW_June)

Sometimes I am asked for a postgres password, once I was asked for the PDW
password (this makes sense and which I did enter).

When I entered the password it either said password failed or it simply
went back to the root command.

No database information was sent.

pg_hba.conf sets method as md5

I don't know what is meant by your reference to \1.

Bob

Lets go back to the beginning.
Basic info:
Pg version
OS
How do you normally connect to the database?
Have you set up passwords for Postgres users?
Remember Postgres users are not the same as system users. So when it asking for
a password it is for the Postgres users password not the system users password
of the same name.

--
Adrian Klaver
adrian.klaver@gmail.com

Version 8.4.1
OS Win 7
I connect through PG Admin (plus an interface I use when the database is up
and running)
The password for this connection is the password I set up during the
installation. this is the same password I am using for the restore
connection.
I haven`t done anything for the postgres user other than what PG Admin uses.

Bob

-----Original Message-----
From: Adrian Klaver
Sent: Friday, December 24, 2010 4:09 PM
To: pgsql-general@postgresql.org
Cc: Bob Pawley ; bricklen
Subject: Re: [GENERAL] Restore

On Friday 24 December 2010 4:03:52 pm Bob Pawley wrote:

I have tried this a number of times -
psql –U postgres PDW < PDW_June_10.sql. (with and without spaces between U
& postgres and/or < and PDW_June)

Sometimes I am asked for a postgres password, once I was asked for the PDW
password (this makes sense and which I did enter).

When I entered the password it either said password failed or it simply
went back to the root command.

No database information was sent.

pg_hba.conf sets method as md5

I don't know what is meant by your reference to \1.

Bob

Lets go back to the beginning.
Basic info:
Pg version
OS
How do you normally connect to the database?
Have you set up passwords for Postgres users?
Remember Postgres users are not the same as system users. So when it asking
for
a password it is for the Postgres users password not the system users
password
of the same name.

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Friday 24 December 2010 4:20:13 pm Bob Pawley wrote:

Version 8.4.1
OS Win 7
I connect through PG Admin (plus an interface I use when the database is up
and running)
The password for this connection is the password I set up during the
installation. this is the same password I am using for the restore
connection.

The installation of what Postgres or PgAdmin?
I don't use PgAdmin so I going out on a limb here. Is there a way in PgAdmin to
look at users? If so it should show you whether the postgres user has a
password.

I haven`t done anything for the postgres user other than what PG Admin
uses.

Bob

--
Adrian Klaver
adrian.klaver@gmail.com

On Fri, Dec 24, 2010 at 5:03 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

I have tried this a number of times -
psql –U postgres PDW < PDW_June_10.sql. (with and without spaces between U &
postgres and/or < and PDW_June)

Sometimes I am asked for a postgres password, once I was asked for the PDW
password (this makes sense and which I did enter).

When I entered the password it either said password failed or it simply went
back to the root command.

Keep in mind, the password set for the unix account is NOT related to
the password set for a user in postgresql with alter user password =
'mypassword';.

If you are entering a unix password for a pg account that hasn't had
the pw set, then it's not going to work. They're not related at all.

In the windows install PG Admin comes packaged with PostgreSQL.

I`m a little confused. My PDW database has postgres as an owner.

You refer to postgres as a user.

My PG Admin shows postgres as a database along with PDW and
template_postgis.

The postgres database probably has a password.

Maybe I need to delete the postgres database.

Bob

-----Original Message-----
From: Adrian Klaver
Sent: Friday, December 24, 2010 4:27 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org ; bricklen
Subject: Re: [GENERAL] Restore

On Friday 24 December 2010 4:20:13 pm Bob Pawley wrote:

Version 8.4.1
OS Win 7
I connect through PG Admin (plus an interface I use when the database is
up
and running)
The password for this connection is the password I set up during the
installation. this is the same password I am using for the restore
connection.

The installation of what Postgres or PgAdmin?
I don't use PgAdmin so I going out on a limb here. Is there a way in PgAdmin
to
look at users? If so it should show you whether the postgres user has a
password.

I haven`t done anything for the postgres user other than what PG Admin
uses.

Bob

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Dec 24, 2010 at 6:00 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

In the windows install PG Admin comes packaged with PostgreSQL.

I`m a little confused. My PDW database has postgres as an owner.

You refer to postgres as a user.

My PG Admin shows postgres as a database along with PDW and
template_postgis.

And there's also a postgres user. Change pg_hba.conf to use trust,
reload pgsql, log in, and issue a \du and you'll see all the users.

The postgres database probably has a password.

databases do not have individual passwords. Users do.

Maybe I need to delete the postgres database.

No, that won't change this issue.

Whether or not a particular connection type needs a password or not is
determined by the pg_hba.conf file. Change that to trust, ident (for
local connections) or md5 as you need. First match is what you need.
I.e. if you have local unix socket and tcp connections set to trust,
then later entries won't really matter. pg_hba.conf is pretty close
to self documenting really.

After setting it reload or restart pgsql and you can log in according
to the method you set in pg_hba.conf.

On Friday 24 December 2010 5:00:19 pm Bob Pawley wrote:

In the windows install PG Admin comes packaged with PostgreSQL.

I`m a little confused. My PDW database has postgres as an owner.

You refer to postgres as a user.

The default superuser for Postgres is the user postgres. An owner needs to be a
user(role actually) so the user that owns PDW is postgres. To make things a
little more complicated the system user that Postgres is run as is also usually
called postgres. This is why I am trying to figure out which password you are
using. In order for it to work to connect to Postgres it needs to be the
password associated with the database user postgres not the system user
postgres. If you are using the password that the Postgres installer used to set
up the system postgres user that is the wrong one. The one you want is the one
you used when you set up the Server properties in PgAdmin.

My PG Admin shows postgres as a database along with PDW and
template_postgis.

The postgres database is a system db set up along with template0 and template1
when a Postgres database cluster is first created.

The postgres database probably has a password.

Maybe I need to delete the postgres database.

No don't do that. It is basically empty and can be recreated if needed but there
is no need to delete it.

Bob

--
Adrian Klaver
adrian.klaver@gmail.com

I just stopped the server after changing config to trust and I got the
message `System error 5 has occurred. Access is denied`

Perhaps this is a clue.

Bob

-----Original Message-----
From: Adrian Klaver
Sent: Friday, December 24, 2010 5:15 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org ; bricklen
Subject: Re: [GENERAL] Restore

On Friday 24 December 2010 5:00:19 pm Bob Pawley wrote:

In the windows install PG Admin comes packaged with PostgreSQL.

I`m a little confused. My PDW database has postgres as an owner.

You refer to postgres as a user.

The default superuser for Postgres is the user postgres. An owner needs to
be a
user(role actually) so the user that owns PDW is postgres. To make things a
little more complicated the system user that Postgres is run as is also
usually
called postgres. This is why I am trying to figure out which password you
are
using. In order for it to work to connect to Postgres it needs to be the
password associated with the database user postgres not the system user
postgres. If you are using the password that the Postgres installer used to
set
up the system postgres user that is the wrong one. The one you want is the
one
you used when you set up the Server properties in PgAdmin.

My PG Admin shows postgres as a database along with PDW and
template_postgis.

The postgres database is a system db set up along with template0 and
template1
when a Postgres database cluster is first created.

The postgres database probably has a password.

Maybe I need to delete the postgres database.

No don't do that. It is basically empty and can be recreated if needed but
there
is no need to delete it.

Bob

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Dec 24, 2010 at 6:20 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

I just stopped the server after changing config to trust and I got the
message `System error 5 has occurred. Access is denied`

What exactly did you type. copy and paste, don't transcribe bits and pieces.

On Friday 24 December 2010 5:20:50 pm Bob Pawley wrote:

I just stopped the server after changing config to trust and I got the
message `System error 5 has occurred. Access is denied`

Perhaps this is a clue.

Bob

Does not meaning anything to me. Please lets not push more buttons:)

--
Adrian Klaver
adrian.klaver@gmail.com

I didn't copy or paste anything. I just clicked Stop.

Bob

-----Original Message-----
From: Scott Marlowe
Sent: Friday, December 24, 2010 5:41 PM
To: Bob Pawley
Cc: adrian.klaver@gmail.com ; pgsql-general@postgresql.org ; bricklen
Subject: Re: [GENERAL] Restore

On Fri, Dec 24, 2010 at 6:20 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

I just stopped the server after changing config to trust and I got the
message `System error 5 has occurred. Access is denied`

What exactly did you type. copy and paste, don't transcribe bits and
pieces.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Dec 24, 2010 at 7:02 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

I didn't copy or paste anything. I just clicked Stop.

No, copy and paste WHAT YOU DID, from your screen into the email. Like this:

smarlowe@breckenridge:~$ psql
psql (8.4.5)
Type "help" for help.

smarlowe=# create user joe;
CREATE ROLE
smarlowe=# alter user joe with password='xyz';
ERROR: syntax error at or near "="
LINE 1: alter user joe with password='xyz';
^
smarlowe=# alter user joe with password 'xyz';
ALTER ROLE
smarlowe=# \q
smarlowe@breckenridge:~$ psql -U jpe
psql: FATAL: Ident authentication failed for user "jpe"
smarlowe@breckenridge:~$ psql -U joe
psql: FATAL: Ident authentication failed for user "joe"
smarlowe@breckenridge:~$

So we're not guessing at what you're seeing.

On Fri, Dec 24, 2010 at 5:03 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

I don't know what is meant by your reference to  \1.

That's a \l the letter l, not a number. It lists the databases in
your installation. If you can get in by psql then \l should work.

On 12/24/10 6:09 PM, Scott Marlowe wrote:

On Fri, Dec 24, 2010 at 7:02 PM, Bob Pawley<rjpawley@shaw.ca> wrote:

I didn't copy or paste anything. I just clicked Stop.

No, copy and paste WHAT YOU DID, from your screen into the email. Like this:

he's on MS Windows 7. you want him to email screen shots? please,
spare us.