Restore database from files (not dump files)?

wheel wrote:

Can a pgsql 8.1 database be restored from the raw file? For one database
I have only the files found in the base folder, example C:\PostgreSQL
\data\base\16404, there are many files called 1247, 1248, etc (no
extension). I think these are the files that make up the database? For
this db I don't have a dump file, just what is on disk. I do know the
users and passwords etc. I've hunted around quite a bit in various
places and most of the restore discussions center around files created
with pg_dump.

Uh, do you have the entier /data directory tree? If so, just restore
the directory start it up a binary.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

In article <200612011732.kB1HWUk19778@momjian.us>, bruce@momjian.us
says...

wheel wrote:

Can a pgsql 8.1 database be restored from the raw file? For one database
I have only the files found in the base folder, example C:\PostgreSQL
\data\base\16404, there are many files called 1247, 1248, etc (no
extension). I think these are the files that make up the database? For
this db I don't have a dump file, just what is on disk. I do know the
users and passwords etc. I've hunted around quite a bit in various
places and most of the restore discussions center around files created
with pg_dump.

Uh, do you have the entier /data directory tree? If so, just restore
the directory start it up a binary.

Yes I have the entire dir/file set. But what does "If so, just restore
the directory start it up a binary" mean? Restore the dir, you mean copy
it to it's location under \base? What does start it up a(s?) binary
mean? I'm new to postgres.

I have copied the folders back to the base dir (like C:\PostgreSQL\data
\base\16404) if that's step one but what after that?

wheel wrote:

In article <200612011732.kB1HWUk19778@momjian.us>, bruce@momjian.us
says...

wheel wrote:

Can a pgsql 8.1 database be restored from the raw file? For one database
I have only the files found in the base folder, example C:\PostgreSQL
\data\base\16404, there are many files called 1247, 1248, etc (no
extension). I think these are the files that make up the database? For
this db I don't have a dump file, just what is on disk. I do know the
users and passwords etc. I've hunted around quite a bit in various
places and most of the restore discussions center around files created
with pg_dump.

Uh, do you have the entier /data directory tree? If so, just restore
the directory start it up a binary.

Yes I have the entire dir/file set. But what does "If so, just restore
the directory start it up a binary" mean? Restore the dir, you mean copy
it to it's location under \base? What does start it up a(s?) binary
mean? I'm new to postgres.

I have copied the folders back to the base dir (like C:\PostgreSQL\data
\base\16404) if that's step one but what after that?

Just start Postgres. If the data dir is ok, it should run fine.

I'm assuming the binary is 8.1 but are the data files from the same version?

b

In article <4573282A.4040605@zijn-digital.com>, brian@zijn-digital.com
says...

wheel wrote:

In article <200612011732.kB1HWUk19778@momjian.us>, bruce@momjian.us
says...

wheel wrote:

Can a pgsql 8.1 database be restored from the raw file? For one database
I have only the files found in the base folder, example C:\PostgreSQL
\data\base\16404, there are many files called 1247, 1248, etc (no
extension). I think these are the files that make up the database? For
this db I don't have a dump file, just what is on disk. I do know the
users and passwords etc. I've hunted around quite a bit in various
places and most of the restore discussions center around files created
with pg_dump.

Uh, do you have the entier /data directory tree? If so, just restore
the directory start it up a binary.

Yes I have the entire dir/file set. But what does "If so, just restore
the directory start it up a binary" mean? Restore the dir, you mean copy
it to it's location under \base? What does start it up a(s?) binary
mean? I'm new to postgres.

I have copied the folders back to the base dir (like C:\PostgreSQL\data
\base\16404) if that's step one but what after that?

Just start Postgres. If the data dir is ok, it should run fine.

I'm assuming the binary is 8.1 but are the data files from the same version?

b

I see...I'm using pgAdmin III with 8.1.5, and the dbs don't reappear in
the pgAdmin list of databases automatically. I'll see if I can figure
out how to get pgAdmin to 'see' the dbs that have been moved here. At
least it seems from what you're saying it's just a matter of the
restored dbs being invisible to pgAdmin.

I have copied the folders back to the base dir (like C:\PostgreSQL
\data
\base\16404) if that's step one but what after that?

Just start Postgres. If the data dir is ok, it should run fine.

It's unclear from your description whether the raw DB files were
moved from another installation - note that the above is only
guaranteed to work if architecture/compiler/etc. are all the same.
If the files were created by exactly the same PG instance, then you
should be okay.

- John D. Burger
MITRE

In article <74A84126-07CC-458C-B4FD-ECD2431831D8@mitre.org>,
john@mitre.org says...

I have copied the folders back to the base dir (like C:\PostgreSQL
\data
\base\16404) if that's step one but what after that?

Just start Postgres. If the data dir is ok, it should run fine.

It's unclear from your description whether the raw DB files were
moved from another installation - note that the above is only
guaranteed to work if architecture/compiler/etc. are all the same.
If the files were created by exactly the same PG instance, then you
should be okay.

- John D. Burger
MITRE

The raw db files are not from this installation, but same ver of windows
binary pg. Does this mean the db is not going to be useable/recoverable?

[ The author's email address is clearly bogus, so I can't privately send
him email.]

You seems to be trying to get help by supplying the least amount of
information possible, and not even fully typing out words. Such
approaches usually fail.

I suggest we ignore further emails from this person until more complete
information is provided, and a valid email address.

---------------------------------------------------------------------------

wheel wrote:

In article <74A84126-07CC-458C-B4FD-ECD2431831D8@mitre.org>,
john@mitre.org says...

I have copied the folders back to the base dir (like C:\PostgreSQL
\data
\base\16404) if that's step one but what after that?

Just start Postgres. If the data dir is ok, it should run fine.

It's unclear from your description whether the raw DB files were
moved from another installation - note that the above is only
guaranteed to work if architecture/compiler/etc. are all the same.
If the files were created by exactly the same PG instance, then you
should be okay.

- John D. Burger
MITRE

The raw db files are not from this installation, but same ver of windows
binary pg. Does this mean the db is not going to be useable/recoverable?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

In article <200612051757.kB5HvaW13981@momjian.us>, bruce@momjian.us
says...

[ The author's email address is clearly bogus, so I can't privately send
him email.]

You seems to be trying to get help by supplying the least amount of
information possible, and not even fully typing out words. Such
approaches usually fail.

I suggest we ignore further emails from this person until more complete
information is provided, and a valid email address.

---------------------------------------------------------------------------

wheel wrote:

In article <74A84126-07CC-458C-B4FD-ECD2431831D8@mitre.org>,
john@mitre.org says...

I have copied the folders back to the base dir (like C:\PostgreSQL
\data
\base\16404) if that's step one but what after that?

Just start Postgres. If the data dir is ok, it should run fine.

It's unclear from your description whether the raw DB files were
moved from another installation - note that the above is only
guaranteed to work if architecture/compiler/etc. are all the same.
If the files were created by exactly the same PG instance, then you
should be okay.

- John D. Burger
MITRE

The raw db files are not from this installation, but same ver of windows
binary pg. Does this mean the db is not going to be useable/recoverable?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Wow, what an unfriendly dude! Obviously

db = database
ver = version
pg = postgres

I am sorry that I don't already know it all about postgres. I've asked
to me seems to be a very simple question and tried to clarify. Newbies
sometimes don't know now to phrase things, so it's not surprising if the
questions aren't totally lucid to experts. If you can't deal with a lack
of precision from new users, then you might consider at least leaving
them alone. You don't have to be rude and start telling the other
experts to give the new person the cold shoulder.

Welcome to postgres, EnterpriseDB, "backup", and here is your kick in
the ass? It's not very encouraging that the rest of the you had no
reaction.

I don't list my email in newsgroups because they're primary resources
for spammers.

On Wed, 2006-12-06 at 14:16, wheel wrote:

In article <200612051757.kB5HvaW13981@momjian.us>, bruce@momjian.us
says...

[ The author's email address is clearly bogus, so I can't privately send
him email.]

You seems to be trying to get help by supplying the least amount of
information possible, and not even fully typing out words. Such
approaches usually fail.

I suggest we ignore further emails from this person until more complete
information is provided, and a valid email address.

---------------------------------------------------------------------------

wheel wrote:

In article <74A84126-07CC-458C-B4FD-ECD2431831D8@mitre.org>,
john@mitre.org says...

I have copied the folders back to the base dir (like C:\PostgreSQL
\data
\base\16404) if that's step one but what after that?

Just start Postgres. If the data dir is ok, it should run fine.

It's unclear from your description whether the raw DB files were
moved from another installation - note that the above is only
guaranteed to work if architecture/compiler/etc. are all the same.
If the files were created by exactly the same PG instance, then you
should be okay.

- John D. Burger
MITRE

The raw db files are not from this installation, but same ver of windows
binary pg. Does this mean the db is not going to be useable/recoverable?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Wow, what an unfriendly dude! Obviously

Actually, all the postgresql hackers are pretty nice people.

But remember one thing. They owe you nothing. No one on this list
does. That said, this is one of the friendliest and most useful lists
I've ever been on.

db = database
ver = version
pg = postgres

uh? What does that mean, exactly?

I am sorry that I don't already know it all about postgres. I've asked
to me seems to be a very simple question and tried to clarify. Newbies
sometimes don't know now to phrase things, so it's not surprising if the
questions aren't totally lucid to experts. If you can't deal with a lack
of precision from new users, then you might consider at least leaving
them alone. You don't have to be rude and start telling the other
experts to give the new person the cold shoulder.

Actually, what bothers me is that your email address is bogus. I get a
ton of spam every day. If you wanna be a member of a mailing list, get
a good spam filter. gmail is VERY good at filtering spam, I suggest you
get an account there and use that to join the list. Otherwise you're
kind of a "ghost" here.

Welcome to postgres, EnterpriseDB, "backup", and here is your kick in
the ass? It's not very encouraging that the rest of the you had no
reaction.

I had one. It was to ignore you because you had a bogus email address.
I'm fairly certain that was a common response.

I don't list my email in newsgroups because they're primary resources
for spammers.

Then get a gmail account.

OK, to your question / answer.

YOU NEED THE WHOLE DATA DIR. not parts of it.

the data dir and everything in it. You can't just move parts of the
base directory around and expect it to work. i.e. copying
data/base/1705/12345 somewhere else is NOT gonna work.

Get a real email address, and join the list.

On Wed, Dec 06, 2006 at 12:16:35PM -0800, wheel wrote:

re Bruce Momjian

Wow, what an unfriendly dude!

Well, he's one of the very guys who make all this
(PostgreSQL, that is) happen for us.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

I don't list my email in newsgroups because they're primary resources
for spammers.

A lot of users will create a Junk email address specifically for this and other lists.

Please realize that any user who's emails get bounced because of false email addresses, exceeded
disks quotas, or automated replying will end up creating a lot of unwanted traffic(i.e. spam) for
everyone that participates in in the tread with this user.

It is a courtesy to everyone on the list to provide a working email address.

Regards,

Richard Broersma Jr.

In article <20061206212608.GN6835@merkur.hilbert.loc>,
Karsten.Hilbert@gmx.net says...

On Wed, Dec 06, 2006 at 12:16:35PM -0800, wheel wrote:

re Bruce Momjian

Wow, what an unfriendly dude!

Well, he's one of the very guys who make all this
(PostgreSQL, that is) happen for us.

Karsten

Yes I know he's a mainstay here, and figured the crew wouldn't want to
ruffle his feathers with an admonishment.

On Thu, 2006-12-07 at 02:41, wheel wrote:

In article <20061206212608.GN6835@merkur.hilbert.loc>,
Karsten.Hilbert@gmx.net says...

On Wed, Dec 06, 2006 at 12:16:35PM -0800, wheel wrote:

re Bruce Momjian

Wow, what an unfriendly dude!

Well, he's one of the very guys who make all this
(PostgreSQL, that is) happen for us.

Karsten

Yes I know he's a mainstay here, and figured the crew wouldn't want to
ruffle his feathers with an admonishment.

Let's add up the score:

Bruce:
works hard on the pgsql project
gives selflessly of his time to help newbies
is a genuinely pleasant guy to hang out with
shows respect for people.
is often the voice of moderation in discussions on the list
has a working email address so I can respond to him if needs be

Some person named "wheel":
has committed zero time or effort to this project
has helped no one on the list, including himself
has been singularly unpleasant to deal with
shows no respect for those trying to help him
is a divisive voice on an otherwise pleasant mailing list
has no working email address, so I cannot be sure he will ever get this.

I notice you didn't reply to my answer to your question. Not a howdy, a
thanks or a request for clarification.

Please, go use a different database, seriously.

Uh, do you have the entier /data directory tree? If so, just restore
the directory start it up a binary.

Yes I have the entire dir/file set. But what does "If so, just restore
the directory start it up a binary" mean? Restore the dir, you mean copy
it to it's location under \base? What does start it up a(s?) binary
mean? I'm new to postgres.

I have copied the folders back to the base dir (like C:\PostgreSQL\data
\base\16404) if that's step one but what after that?

OK, one more try:

- Stop postgres.

- Restore the ***ENTIRE*** ***data*** directory tree. Not "base", not
something else "to it's location under \base". The entire data directory
tree, just like the helpful message said.

- Start postgres.

Now, if you did that, and the raw files were from the same OS & architecture
& postgres build settings, everything is fine. Otherwise, you're out of
luck.

I see...I'm using pgAdmin III with 8.1.5, and the dbs don't reappear in
the pgAdmin list of databases automatically.

I doubt that.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

In article <MPG.1fe17d71afa77c5b989742@newsgroups.comcast.net>,
wheel <wheel@wheel.not> wrote:

% I copied all of the database 'parts' to the new 'base' directory. I am
% not sure how carefully anyone has read what I wrote. But it's so simple
% what I'm asking about, or so it would seem to me.

As several people have pointed out, you can't do this.

% In another install of pg (assuming BM is not reading this and won't be
% upset by abbreviations) there was a database at this location:
%
% c:\postgresql\data\base\16404
%
% at least I think that is a database folder, I've never read anything
% about where/how postgres stores the files for a database. Under the
% 16404 folder are many other folders, I think they comprise that
% particular database.
%
% On a separate postgres installation, ie different server, I want to move
% that 16404 folder (with child dirs etc, what I assume are it's parts) to
% what amounts to the same location:
%
% c:\postgresql\data\base\16404

As several people have pointed out, this will not work. You can copy
c:\postgresql\data and everything under it, but you can't copy
individual subdirectories and have it work.

--

Patrick TJ McPhee
North York Canada
ptjm@interlog.com

In article <12nhtgngknodu64@corp.supernews.com>, ptjm@interlog.com
says...

In article <MPG.1fe17d71afa77c5b989742@newsgroups.comcast.net>,
wheel <wheel@wheel.not> wrote:

% I copied all of the database 'parts' to the new 'base' directory. I am
% not sure how carefully anyone has read what I wrote. But it's so simple
% what I'm asking about, or so it would seem to me.

As several people have pointed out, you can't do this.

% In another install of pg (assuming BM is not reading this and won't be
% upset by abbreviations) there was a database at this location:
%
% c:\postgresql\data\base\16404
%
% at least I think that is a database folder, I've never read anything
% about where/how postgres stores the files for a database. Under the
% 16404 folder are many other folders, I think they comprise that
% particular database.
%
% On a separate postgres installation, ie different server, I want to move
% that 16404 folder (with child dirs etc, what I assume are it's parts) to
% what amounts to the same location:
%
% c:\postgresql\data\base\16404

As several people have pointed out, this will not work. You can copy
c:\postgresql\data and everything under it, but you can't copy
individual subdirectories and have it work.

I see what you and they were talking about now.

The reason I'd not understood before what that when told that it doesn't
work to copy individual subdirs, was because as I'd stated I wasn't 100%
sure that the dirs like c:\postgresql\data\base\16404\ represented the
top level of a distinct db's directory structure, and had mentioned one
of that folder's subdirectories as an example of what I'd copied along
with c:\postgresql\data\base\16404\. That subdir is what I thought
people were referring to when they said you can't selectively copy
subdirectories. After that I more or less stubbornly misinterpreted what
was being recommended in that light.

I guess the reason is that the pg system database etc are lodged in the
hive under \base\, and the system db contains the metadata about the db
to be restored?

Unfortunately I've tried this now (exact same version of pg windows
binary, user, password etc) and it didn't work. I'll just forget about
the db I was trying to restore, it wasn't important, and has certainly
not been worth the train wreck intro to the group.

In article <C19E2E1F.5D22D%scott_ribe@killerbytes.com>,
scott_ribe@killerbytes.com says...

Uh, do you have the entier /data directory tree? If so, just restore
the directory start it up a binary.

Yes I have the entire dir/file set. But what does "If so, just restore
the directory start it up a binary" mean? Restore the dir, you mean copy
it to it's location under \base? What does start it up a(s?) binary
mean? I'm new to postgres.

I have copied the folders back to the base dir (like C:\PostgreSQL\data
\base\16404) if that's step one but what after that?

OK, one more try:

- Stop postgres.

- Restore the ***ENTIRE*** ***data*** directory tree. Not "base", not
something else "to it's location under \base". The entire data directory
tree, just like the helpful message said.

- Start postgres.

Now, if you did that, and the raw files were from the same OS & architecture
& postgres build settings, everything is fine. Otherwise, you're out of
luck.

I see...I'm using pgAdmin III with 8.1.5, and the dbs don't reappear in
the pgAdmin list of databases automatically.

I doubt that.

Via Patrick's post I realized what my misstep was, but unforutnately it
has not resulted in a restored database in my case, I'm not sure why.
But thanks for the pointer, I'm sure what you've written is correct,
there is just some condition that prevents success in this case. It is
same OS, exact version of Postgres, everything that I'm aware of is a
match, but something is obviously at variance.

wheel wrote:

I guess the reason is that the pg system database etc are lodged in the
hive under \base\, and the system db contains the metadata about the db
to be restored?

Actually, the users etc. are stored in \global\
The transaction log (WAL) is in \pg_xlog\
And so on...

Unfortunately I've tried this now (exact same version of pg windows
binary, user, password etc) and it didn't work. I'll just forget about
the db I was trying to restore, it wasn't important, and has certainly
not been worth the train wreck intro to the group.

Hmm - certainly should work.

--
Richard Huxton
Archonet Ltd

wheel <wheel@wheel.not> writes:

I guess the reason is that the pg system database etc are lodged in the
hive under \base\, and the system db contains the metadata about the db
to be restored?

No, the reason why selective restore doesn't work is that all tables in
a database cluster depend on the same commit log (pg_clog) to determine
which rows are valid and which are not. What you were trying to do is
described in the manual (with a warning not to do it) under
backup/restore procedures:
http://www.postgresql.org/docs/8.2/static/backup-file.html

Also, if you would like to know more about the physical layout of the
database contents, it's described here:
http://www.postgresql.org/docs/8.2/static/storage.html

regards, tom lane