Re: Restore database from files (not dump files)?

[ 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(at)mitre(dot)org>,
> > john(at)mitre(dot)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(at)momjian(dot)us
> EnterpriseDB http://www.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

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

In article <200612051757(dot)kB5HvaW13981(at)momjian(dot)us>, bruce(at)momjian(dot)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(at)mitre(dot)org>,
> > > john(at)mitre(dot)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(at)momjian(dot)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(dot)kB5HvaW13981(at)momjian(dot)us>, bruce(at)momjian(dot)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(at)mitre(dot)org>,
> > > > john(at)mitre(dot)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(at)momjian(dot)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(dot)GN6835(at)merkur(dot)hilbert(dot)loc>,
Karsten(dot)Hilbert(at)gmx(dot)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(dot)GN6835(at)merkur(dot)hilbert(dot)loc>,
> Karsten(dot)Hilbert(at)gmx(dot)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.

In article <MPG(dot)1fe17d71afa77c5b989742(at)newsgroups(dot)comcast(dot)net>,
wheel <wheel(at)wheel(dot)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(at)interlog(dot)com

In article <12nhtgngknodu64(at)corp(dot)supernews(dot)com>, ptjm(at)interlog(dot)com
says...
> In article <MPG(dot)1fe17d71afa77c5b989742(at)newsgroups(dot)comcast(dot)net>,
> wheel <wheel(at)wheel(dot)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.

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(at)wheel(dot)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

Tom Lane wrote:
> wheel <wheel(at)wheel(dot)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

This is the contents of /data:

PG_VERSION pg_clog/ pg_multixact/
pg_twophase/ postmaster.opts base/
pg_hba.conf pg_subtrans/ pg_xlog/
postmaster.pid global/ pg_ident.conf
pg_tblspc/ postgresql.conf

None of these are optional for restoring a database. They are all
interconected.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

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