Thread: Restore database from files (not dump files)?
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.
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 <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.
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.
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
Tom Lane wrote: > 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 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@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +