Thread: windows and pg 8.2 (change database to another server)
Hello all, I would like to change a pg database to another server. The source environment is: postgresql Windows v.8.2.4 (windows xp workstation). The target environment is: postgresql Windows v.8.2.5 (windows 2003 Server). I would like to do migration without pg_dumpall and I think I can do the migration process _only_ copying this (after stop Postgresql service, of course): 1.- Data directory (c:\program files\postgresql\8.2\data). 2.- Another TableSpace folders/directories. But after copy that folders to my new server, service PostgreSQL doesn't start with a 'cannot create postmaster.pid'. First of all. I think this method is admisible. Isn't it ? And second question: I think my problem is that some rights are wrong after copying data folder. What are the right rights to apply to data folder ? Any other 'direct' migration process ? Greetings...
On Sun, Sep 30, 2007 at 11:36:23PM +0200, Terry Yapt wrote: > Hello all, > > I would like to change a pg database to another server. > > The source environment is: postgresql Windows v.8.2.4 (windows xp > workstation). > The target environment is: postgresql Windows v.8.2.5 (windows 2003 Server). > > I would like to do migration without pg_dumpall and I think I can do the > migration process _only_ copying this (after stop Postgresql service, of > course): > 1.- Data directory (c:\program files\postgresql\8.2\data). > 2.- Another TableSpace folders/directories. > > But after copy that folders to my new server, service PostgreSQL doesn't > start with a 'cannot create postmaster.pid'. > > First of all. I think this method is admisible. Isn't it ? It is. > And second question: I think my problem is that some rights are wrong > after copying data folder. What are the right rights to apply to data > folder ? Yes, most likely. You need to grant the postgres service account "Change" permissions (or Full Control, but Change is recommended) on the data directory. If you didn't do anything speicifically, it will just have inherited from further up in the tree, which means that the service account only has "Read" access. //Magnus
Magnus Hagander escribió: > On Sun, Sep 30, 2007 at 11:36:23PM +0200, Terry Yapt wrote: >> First of all. I think this method is admisible. Isn't it ? >> > > It is. > Glad to read it.... :-) >> And second question: I think my problem is that some rights are wrong >> after copying data folder. What are the right rights to apply to data >> folder ? >> > > Yes, most likely. You need to grant the postgres service account "Change" > permissions (or Full Control, but Change is recommended) on the data > directory. If you didn't do anything speicifically, it will just have > inherited from further up in the tree, which means that the service account > only has "Read" access. > > //Magnus > I have tried a couple of combinations.... none of them was successful. I have tried to assign 'Full Control' to data folder and sub-folders and files. Varying this 'Full Control' preserving inheritance, deleting inheritance. I have tried to assign 'Full Control' to Administrators and SYSTEM accounts/groups too. I have tried to do the same thing over sub-folders, files and so on. Nothing was ok. I have got this application errors (in reverse cronological order, 1=more recent): ===== 1 ==== postgres: could not find the database system Expected to find it in the directory "C:/Archivos de programa/PostgreSQL/8.2/data", but could not open file "C:/Archivos de programa/PostgreSQL/8.2/data/global/pg_control": Permission denied ===== 2 ==== -2007-10-01 23:19:31 PANIC: could not open control file "global/pg_control": Permission denied ===== 3 ==== --2007-10-01 23:17:28 FATAL: could not create lock file "postmaster.pid": Permission denied =========== Greetings...
On Mon, Oct 01, 2007 at 11:38:53PM +0200, Terry Yapt wrote: > Magnus Hagander escribió: > >On Sun, Sep 30, 2007 at 11:36:23PM +0200, Terry Yapt wrote: > >>First of all. I think this method is admisible. Isn't it ? > >> > > > >It is. > > > > Glad to read it.... :-) > > >>And second question: I think my problem is that some rights are wrong > >>after copying data folder. What are the right rights to apply to data > >>folder ? > >> > > > >Yes, most likely. You need to grant the postgres service account "Change" > >permissions (or Full Control, but Change is recommended) on the data > >directory. If you didn't do anything speicifically, it will just have > >inherited from further up in the tree, which means that the service account > >only has "Read" access. > > > >//Magnus > > > > I have tried a couple of combinations.... none of them was successful. > > I have tried to assign 'Full Control' to data folder and sub-folders and > files. Varying this 'Full Control' preserving inheritance, deleting > inheritance. I have tried to assign 'Full Control' to Administrators > and SYSTEM accounts/groups too. I have tried to do the same thing over > sub-folders, files and so on. Your errors certainly indicate it's a permissions issue. You should also veryfi that the read-only flag is not set on any of the files. I don't see how it could become that, but if it is that'll give the same error. Also, check the permissions on c:\, C:/Archivos de programa/ and all teh way down the tree. The postgres service account needs read access there, and write to data and below. Permissions set for SYSTEM and/or administrators make no difference at all to the server. And yes, it shoul dbe set on the file and all subdirs. Use the checkbox to overwrite all permissions on subdirs, that's the fastest way. //Magnus
Hi Magnus and all people... > And yes, it shoul dbe set on the file and all subdirs. Use the checkbox to > overwrite all permissions on subdirs, that's the fastest way. > > //Magnus > > Sorry my delay but I was very busy last week. In general, giving windows Postgres user Change right do the trick but I have done a bit of other things too. I have do a complete delete of all inherited rights and windows-postgres rights. After that I have given change right to postgres-windows-user on data folder and sub-folders and files. Checking out both checkboxes on the bottom: inheritance and follow the tree (I think this is the right translation). All is working like a charm now. Greetings and thanks again.