Where does Postgres store databases?

[scsi2000]

Hi. I'm new to Postgres. I'm using the Windows version.

I am trying to restore a database file. All is going well except my system hard drive (c is full. How can I change where my database is being stored (I'd like to store it on my e: drive).

Is this done when I CREATE my database, or when I restore the database into the empty database I've created?

Thanks.

 

[rski]

The database cluster is the place where u'r tables, views etc lies (I gues u didn't use tablespaces). So u can try just copy hole cluster (environment variable PGDATA show u the cluster's path), but I don't now if it works (I didn't try it). The best way to move database is to backup it (pg_dump command), then create new cluster on e: (initdb with -D option) and then restore databse to the new cluster (command pg_restore). I think it should works. (sorry for poor english)

 

[scsi2000]

THANKS! Where do I type these commands? (I'm a novice). I read documentation for Postgres and I read -D this and -S that, but the DOS-like prompt only allows for /a and /y type switches. Is it the same?

 

[rski]

I didn't user postgres under windows?, don't u use postgres under Cygwin?

 

[scsi2000]

I appreciate all of the thoughts offered so far.

Perhaps I am totally backwards, or maybe Postgres really doesn't make any sense.

Where exactly do I do an initdb? (Postgres for Windows) so that I can change the location of where Postgres stores it's databases.

Is this done from the "command prompt", "psql to 'postgres'" or within "pgAdmin III".

I've read what few tutorials are out there on how to initialize a database, but nobody ever starts with a quick note about WHERE to type these commands, they just say, type them. I could type them in Notepad, but that's not going to do me any good, right?

Also, how do I change what user I am logged in as (chuser?), apparently there are some functions such as 'initdb' that seem to be very administratorial in their nature, but against all logic (I guess I understand the security justification for this way of doing things), I must actually login as an "UNprivledged user", in order to do an initdb? CRAZY!!?

So, where do I change who I am logged in as.

In short, I want to:

- Log into windows with the correct account
(is that Admin or Guest?)
- Open up the correct program
(is that "command prompt", "psql to
'postgres'" or "pgAdmin"?)
- Type the correct command to give me the correct
privileges to run "initdb" command.
- Type the correct command ("initdb"?) to setup all
future databases ("cluster?") in "E:/WhackoSQL/".

Any help?

 

[feherke]

Hi

First of all, I never used PostgreSQL on Windows. But I am sure the same theory applies.

Where exactly do I do an initdb? [gray](...)[/gray] so that I can change the location of where Postgres stores it's databases.

Please note that with the [tt]initdb[/tt] command you do not actually change to database's location. You just initialize another location to make it suitable to store a PostgreSQL database :

initdb -D "E:/WhackoSQL/"

Than you have to tell to the daemon too the location you want to use. A comprehensive draft is in thread699-1354124 . ( Ignore the operating system difference. )

but nobody ever starts with a quick note about WHERE to type these commands, they just say, type them

Of course not. PostgreSQL is native Linux software. There is evident where to type commands. ( You type them in a Command Prompt window. ) Where to type SQL commands is up to anyone's habits. ( I type them in [tt]psql[/tt], but sure no Windows guy use it. ) And [tt]psql[/tt]'s internal commands can not be confused by any previously mentioned, so is evident where to type them.

apparently there are some functions such as 'initdb' that seem to be very administratorial in their nature, but against all logic [gray](...)[/gray], I must actually login as an "UNprivledged user", in order to do an initdb? CRAZY!!?

No, that is completely logic. They explain clearly that "[tt]initdb[/tt] must be run as the user that will own the server process". And daemons does not run with superuser privileges. ( On Windows the superuser ( or root ) probably is a user with account type "Computer administrator" and the unprivileged user a user with account type "Limited". )

Feherke.

http://rootshell.be/~feherke/

 

[scsi2000]

initdb must be run as the user that will own the server process.

I know I don't understand correctly, but doesn't the user "postgres" with the long and overcomplicated encryption string for a password own the process? But isn't he a superuser?

Sincerely,

Discouraged by OpenSource.

 

[feherke]

Hi

doesn't the user "postgres" with the long and overcomplicated encryption string for a password own the process?

There are usually two postgres users :
[ul]
[li]system user - usually unprivileged user, usually the one with who's rights the daemon runs[/li]
[li]database user - usually the database administrator[/li]
[/ul]

Discouraged by OpenSource.

Then you should use proprietary commercial softwares.

Feherke.

http://rootshell.be/~feherke/

 

[scsi2000]

Then you should use proprietary commercial softwares

Oh how I wish I were using my trusty MS SQL Server 2005. Unfortunately, one of my clients has sent me a PostgreSQL database and I've got to deal with it.

Now, when I installed Postgres, I was prompted to create a user that would run the process. The box was pre-filled with user: 'postgress', as the process user. I created a nice password like: "yorba34-9$". Hard enough to guess. Easy enough to remember, right? Wrong. Postgres, says, "Sorry, we're going to change that password so that it's truly secure. Here's your new password: "!@#%#$LKJW%^%^LKJ$#(&*()"&(*#^$)*#&@(#*$*&(*%T^)*@#$%#$"

WHAT THE?!

I started writing down the password around 1pm. Finally about 3pm I was ready to move to the next step.

Now I was asked who the superuser should be. The default was again, "postgres", with a much simpler password of my own choosing.

So is postgres @#$@#$@#$@#$@# the unprivileged process owner/user
and postgres e@sy-pa$$w0rd the superuser?

 

[feherke]

Hi

Sorry, we're going to change that password so that it's truly secure. Here's your new password: "!@#%#$LKJW%^%^LKJ$#(&*()"&(*#^$)*#&@(#*$*&(*%T^)*@#$%#$"

I never saw such message. Are you sure it was PostgreSQL itself and not just a client you use ? I always just run createuser and on development machine I set no password.

Your messages are becoming cryptic. You will have to give us more details about what and how are you doing.

Feherke.

http://rootshell.be/~feherke/