Re: [GENERAL] Starting new cluster from base backup
On 02/18/2015 10:24 AM, Guillaume Drolet wrote: 2015-02-18 11:06 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com: So is E:\ a network drive shared by both machines? No, E:\ is a local drive on which I created a tablespace, in order to have enough space for my database. In my current setup on the source machine, PGDATA is in the default PGSQL installation on the OS disk so space is limited. On the destination machine, PGDATA will be on a different, larger disk than the OS disk. So is there an E:\ drive available on the destination machine? Anyway, in the end I want to move the database that's in that tablespace back to pg_default. I see two possibilities: 1) Moving it now, before taking the base backup, using ALTER DATABASE mydb SET TABLESPACE pg_default; Then I assume I should be able to use -X stream and plain format with pg_basebackup. Or 2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X stream and plain format, copy the tablespace from the source to the destination machine. Create a new symbolic link in data/pg_tblspc on the new machine and point it to the copied tablespace. Are these two approaches feasible? I would say 1 would be more feasible then 2. If you use 2, delete the symlink and do the backup, what happens with any dependencies between objects in the default tablespace and the one you cut out? Also the pg_basebackup will be taking a backup of one part of the cluster at one point in time and the copy of the remote tablespace will possibly be at another point in time. I do no see that ending well. You're probably right about that. My understanding was that, since this is a single-user database (at least for now) on my machine, if I wasn't performing any query or task during the backup, then the problem you mentioned would in fact not be a problem. Except Postgres performs tasks behind the scenes, so changes are happening. There is also still the dependency issue. Thanks. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Starting new cluster from base backup
2015-02-18 13:40 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com: On 02/18/2015 10:24 AM, Guillaume Drolet wrote: 2015-02-18 11:06 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com: So is E:\ a network drive shared by both machines? No, E:\ is a local drive on which I created a tablespace, in order to have enough space for my database. In my current setup on the source machine, PGDATA is in the default PGSQL installation on the OS disk so space is limited. On the destination machine, PGDATA will be on a different, larger disk than the OS disk. So is there an E:\ drive available on the destination machine? Yes there is an E:\ drive available on the destination machine. But for now, these two machines don't communicate. I take the backup on a hot swap disk (F:\) and then swap it into the destination machine. Ultimately when my database will be running on the destination machine, I'll connect to it from other machines in my local network. Anyway, in the end I want to move the database that's in that tablespace back to pg_default. I see two possibilities: 1) Moving it now, before taking the base backup, using ALTER DATABASE mydb SET TABLESPACE pg_default; Then I assume I should be able to use -X stream and plain format with pg_basebackup. Or 2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X stream and plain format, copy the tablespace from the source to the destination machine. Create a new symbolic link in data/pg_tblspc on the new machine and point it to the copied tablespace. Are these two approaches feasible? I would say 1 would be more feasible then 2. If you use 2, delete the symlink and do the backup, what happens with any dependencies between objects in the default tablespace and the one you cut out? Also the pg_basebackup will be taking a backup of one part of the cluster at one point in time and the copy of the remote tablespace will possibly be at another point in time. I do no see that ending well. You're probably right about that. My understanding was that, since this is a single-user database (at least for now) on my machine, if I wasn't performing any query or task during the backup, then the problem you mentioned would in fact not be a problem. Except Postgres performs tasks behind the scenes, so changes are happening. There is also still the dependency issue. Can't the dependency issue be fixed by creating a new junction in data/pg_tblspc that would point to the relocated tablespace? Thanks. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] Starting new cluster from base backup
2015-02-18 11:06 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com: On 02/18/2015 04:26 AM, Guillaume Drolet wrote: 2015-02-17 17:14 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com: On 02/17/2015 06:54 AM, Guillaume Drolet wrote: Adrian: thanks for this information. I tried running pg_basebackup in plain format with option -X stream (pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U postgres -P) but I got the message: pg_basebackup: directory E:\Data\Database exists but is not empty I creatde a tablespace using CREATE TABLESPACE at the location mentioned in the message. According to what I read online about this, this message is issued when a tablespace was created under PGDATA. In my case, only the directory junction pointing to my tablespace (on a different drive than PGDATA) exists under PGDATA, not the tablespace itself. The only way I can run pg_basebackup with WAL files is with option -Ft and -X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes a lot of time to extract when tarred. Is there another way to approach this? All I can come up with at the moment So what is the path on the original machine and can it be replicated on the new machine, at least temporarily? The path on the original (i.e. source) machine is: E:\Data\Database\PG_9.3_201306121\.. I'm thinking if the path can be replicated, let pg_basebackup write to it and then create the tablespace you want and do ALTER TABLE SET TABLESPACE to move the tables. You would also need to do this for indexes. Not sure I understand when you say let pg_basebackup write to it. This tablespace already exists on the source machine so cannot be written over. It needs to be written in the backup so that I can than recreate it on the destination machine. So is E:\ a network drive shared by both machines? No, E:\ is a local drive on which I created a tablespace, in order to have enough space for my database. In my current setup on the source machine, PGDATA is in the default PGSQL installation on the OS disk so space is limited. On the destination machine, PGDATA will be on a different, larger disk than the OS disk. Anyway, in the end I want to move the database that's in that tablespace back to pg_default. I see two possibilities: 1) Moving it now, before taking the base backup, using ALTER DATABASE mydb SET TABLESPACE pg_default; Then I assume I should be able to use -X stream and plain format with pg_basebackup. Or 2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X stream and plain format, copy the tablespace from the source to the destination machine. Create a new symbolic link in data/pg_tblspc on the new machine and point it to the copied tablespace. Are these two approaches feasible? I would say 1 would be more feasible then 2. If you use 2, delete the symlink and do the backup, what happens with any dependencies between objects in the default tablespace and the one you cut out? Also the pg_basebackup will be taking a backup of one part of the cluster at one point in time and the copy of the remote tablespace will possibly be at another point in time. I do no see that ending well. You're probably right about that. My understanding was that, since this is a single-user database (at least for now) on my machine, if I wasn't performing any query or task during the backup, then the problem you mentioned would in fact not be a problem. Thanks. -- Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] Starting new cluster from base backup
2015-02-18 16:11 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com: On 02/18/2015 11:51 AM, Guillaume Drolet wrote: 2015-02-18 13:40 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com: On 02/18/2015 10:24 AM, Guillaume Drolet wrote: 2015-02-18 11:06 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com mailto:adrian.klaver@aklaver.__com mailto:adrian.kla...@aklaver.com: So is E:\ a network drive shared by both machines? No, E:\ is a local drive on which I created a tablespace, in order to have enough space for my database. In my current setup on the source machine, PGDATA is in the default PGSQL installation on the OS disk so space is limited. On the destination machine, PGDATA will be on a different, larger disk than the OS disk. So is there an E:\ drive available on the destination machine? Yes there is an E:\ drive available on the destination machine. But for now, these two machines don't communicate. I take the backup on a hot swap disk (F:\) and then swap it into the destination machine. Ultimately when my database will be running on the destination machine, I'll connect to it from other machines in my local network. So if I understand correctly you have: 1) On source machine a directory E:\Data\Database. 2) On the source machine in Postgres you have a created a tablespace that points at E:\Data\Database. 3) On destination machine you have an E:\ drive also. You're correct Then have you tried: 1) Create \Data\Database directory under E:\ on the destination machine. 2) Do the pg_basebackup. I'm not sure I understand why, at this moment in the sequence of operation, I would create \Data\Database under E:\ on the destination machine. pg_basebackup, when run on the source DB on the source machine, has no idea about the destination machine. Maybe you're confused with the F:\ drive, which is the drive on which I tried to save my base backup with the command: pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U postgres -P This drive (F:\) is not the destination machine, it's a swappable disk I use to move my base backup from one machine (the source) to another (the destination). Can't the dependency issue be fixed by creating a new junction in data/pg_tblspc that would point to the relocated tablespace? The docs say you can: http://www.postgresql.org/docs/9.3/static/manage-ag-tablespaces.html The directory $PGDATA/pg_tblspc contains symbolic links that point to each of the non-built-in tablespaces defined in the cluster. Although not recommended, it is possible to adjust the tablespace layout by hand by redefining these links. Under no circumstances perform this operation while the server is running. Note that in PostgreSQL 9.1 and earlier you will also need to update the pg_tablespace catalog with the new locations. (If you do not, pg_dump will continue to output the old tablespace locations.) I have not done it and I see the Although not recommended.. part above, so I would say that is a last resort solution. I confirm this method works. I've done it in the past using the steps in this blog and its comments: http://www.databasesoup.com/2013/11/moving-tablespaces.html Thanks. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] Starting new cluster from base backup
On 02/18/2015 11:51 AM, Guillaume Drolet wrote: 2015-02-18 13:40 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com: On 02/18/2015 10:24 AM, Guillaume Drolet wrote: 2015-02-18 11:06 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com mailto:adrian.klaver@aklaver.__com mailto:adrian.kla...@aklaver.com: So is E:\ a network drive shared by both machines? No, E:\ is a local drive on which I created a tablespace, in order to have enough space for my database. In my current setup on the source machine, PGDATA is in the default PGSQL installation on the OS disk so space is limited. On the destination machine, PGDATA will be on a different, larger disk than the OS disk. So is there an E:\ drive available on the destination machine? Yes there is an E:\ drive available on the destination machine. But for now, these two machines don't communicate. I take the backup on a hot swap disk (F:\) and then swap it into the destination machine. Ultimately when my database will be running on the destination machine, I'll connect to it from other machines in my local network. So if I understand correctly you have: 1) On source machine a directory E:\Data\Database. 2) On the source machine in Postgres you have a created a tablespace that points at E:\Data\Database. 3) On destination machine you have an E:\ drive also. Then have you tried: 1) Create \Data\Database directory under E:\ on the destination machine. 2) Do the pg_basebackup. Can't the dependency issue be fixed by creating a new junction in data/pg_tblspc that would point to the relocated tablespace? The docs say you can: http://www.postgresql.org/docs/9.3/static/manage-ag-tablespaces.html The directory $PGDATA/pg_tblspc contains symbolic links that point to each of the non-built-in tablespaces defined in the cluster. Although not recommended, it is possible to adjust the tablespace layout by hand by redefining these links. Under no circumstances perform this operation while the server is running. Note that in PostgreSQL 9.1 and earlier you will also need to update the pg_tablespace catalog with the new locations. (If you do not, pg_dump will continue to output the old tablespace locations.) I have not done it and I see the Although not recommended.. part above, so I would say that is a last resort solution. Thanks. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Starting new cluster from base backup
2015-02-17 17:14 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com: On 02/17/2015 06:54 AM, Guillaume Drolet wrote: Adrian: thanks for this information. I tried running pg_basebackup in plain format with option -X stream (pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U postgres -P) but I got the message: pg_basebackup: directory E:\Data\Database exists but is not empty I creatde a tablespace using CREATE TABLESPACE at the location mentioned in the message. According to what I read online about this, this message is issued when a tablespace was created under PGDATA. In my case, only the directory junction pointing to my tablespace (on a different drive than PGDATA) exists under PGDATA, not the tablespace itself. The only way I can run pg_basebackup with WAL files is with option -Ft and -X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes a lot of time to extract when tarred. Is there another way to approach this? All I can come up with at the moment So what is the path on the original machine and can it be replicated on the new machine, at least temporarily? The path on the original (i.e. source) machine is: E:\Data\Database\ PG_9.3_201306121\.. I'm thinking if the path can be replicated, let pg_basebackup write to it and then create the tablespace you want and do ALTER TABLE SET TABLESPACE to move the tables. You would also need to do this for indexes. Not sure I understand when you say let pg_basebackup write to it. This tablespace already exists on the source machine so cannot be written over. It needs to be written in the backup so that I can than recreate it on the destination machine. Anyway, in the end I want to move the database that's in that tablespace back to pg_default. I see two possibilities: 1) Moving it now, before taking the base backup, using ALTER DATABASE mydb SET TABLESPACE pg_default; Then I assume I should be able to use -X stream and plain format with pg_basebackup. Or 2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X stream and plain format, copy the tablespace from the source to the destination machine. Create a new symbolic link in data/pg_tblspc on the new machine and point it to the copied tablespace. Are these two approaches feasible? Thanks. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] Starting new cluster from base backup
On 02/18/2015 04:26 AM, Guillaume Drolet wrote: 2015-02-17 17:14 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com: On 02/17/2015 06:54 AM, Guillaume Drolet wrote: Adrian: thanks for this information. I tried running pg_basebackup in plain format with option -X stream (pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U postgres -P) but I got the message: pg_basebackup: directory E:\Data\Database exists but is not empty I creatde a tablespace using CREATE TABLESPACE at the location mentioned in the message. According to what I read online about this, this message is issued when a tablespace was created under PGDATA. In my case, only the directory junction pointing to my tablespace (on a different drive than PGDATA) exists under PGDATA, not the tablespace itself. The only way I can run pg_basebackup with WAL files is with option -Ft and -X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes a lot of time to extract when tarred. Is there another way to approach this? All I can come up with at the moment So what is the path on the original machine and can it be replicated on the new machine, at least temporarily? The path on the original (i.e. source) machine is: E:\Data\Database\PG_9.3_201306121\.. I'm thinking if the path can be replicated, let pg_basebackup write to it and then create the tablespace you want and do ALTER TABLE SET TABLESPACE to move the tables. You would also need to do this for indexes. Not sure I understand when you say let pg_basebackup write to it. This tablespace already exists on the source machine so cannot be written over. It needs to be written in the backup so that I can than recreate it on the destination machine. So is E:\ a network drive shared by both machines? Anyway, in the end I want to move the database that's in that tablespace back to pg_default. I see two possibilities: 1) Moving it now, before taking the base backup, using ALTER DATABASE mydb SET TABLESPACE pg_default; Then I assume I should be able to use -X stream and plain format with pg_basebackup. Or 2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X stream and plain format, copy the tablespace from the source to the destination machine. Create a new symbolic link in data/pg_tblspc on the new machine and point it to the copied tablespace. Are these two approaches feasible? I would say 1 would be more feasible then 2. If you use 2, delete the symlink and do the backup, what happens with any dependencies between objects in the default tablespace and the one you cut out? Also the pg_basebackup will be taking a backup of one part of the cluster at one point in time and the copy of the remote tablespace will possibly be at another point in time. I do no see that ending well. Thanks. -- Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Starting new cluster from base backup
On 02/18/2015 01:48 PM, Guillaume Drolet wrote: So if I understand correctly you have: 1) On source machine a directory E:\Data\Database. 2) On the source machine in Postgres you have a created a tablespace that points at E:\Data\Database. 3) On destination machine you have an E:\ drive also. You're correct Then have you tried: 1) Create \Data\Database directory under E:\ on the destination machine. 2) Do the pg_basebackup. I'm not sure I understand why, at this moment in the sequence of operation, I would create \Data\Database under E:\ on the destination machine. pg_basebackup, when run on the source DB on the source machine, has no idea about the destination machine. Maybe you're confused with the F:\ drive, which is the drive on which I tried to save my base backup with the command: I am confused, but not about F:\ drive:). My confusion was on where the error directory E:\Data\Database exists but is not empty occurred. I just ran a test. So the issue is in plain mode pg_basebackup does the binary copy to F:\208376PT\db which is fine. The problem is that it can still see E:\Data\Database on the source machine, so when it tries to set up the copy of the tablespace it sees that the directory is not empty and stops. So the only way this going to work in 9.3 with plain is to copy not to F:\ but to the destination machine directly. I am guessing that is not possible? It works in the tar case because the tablespace directory gets renamed. pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U postgres -P This drive (F:\) is not the destination machine, it's a swappable disk I use to move my base backup from one machine (the source) to another (the destination). I have not done it and I see the Although not recommended.. part above, so I would say that is a last resort solution. I confirm this method works. I've done it in the past using the steps in this blog and its comments: http://www.databasesoup.com/2013/11/moving-tablespaces.html Interesting post, I missed it the first time around. Seems worth a try. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Starting new cluster from base backup
Adrian: thanks for this information. I tried running pg_basebackup in plain format with option -X stream (pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U postgres -P) but I got the message: pg_basebackup: directory E:\Data\Database exists but is not empty I creatde a tablespace using CREATE TABLESPACE at the location mentioned in the message. According to what I read online about this, this message is issued when a tablespace was created under PGDATA. In my case, only the directory junction pointing to my tablespace (on a different drive than PGDATA) exists under PGDATA, not the tablespace itself. The only way I can run pg_basebackup with WAL files is with option -Ft and -X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes a lot of time to extract when tarred. Is there another way to approach this? Thanks. 2015-02-16 15:21 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com: On 02/16/2015 11:31 AM, Guillaume Drolet wrote: Dear listers, I want to move a cluster from one machine to another. I used pg_basebackup to create an archive and copied/extracted it over the old PGDATA location on the new machine (the server was stopped). If I start pgsql I get these messages in my log file: 2015-02-16 14:29:12 EST LOG: database system was interrupted; last known up at 2015-02-07 06:31:41 EST 2015-02-16 14:29:12 EST LOG: invalid checkpoint record 2015-02-16 14:29:12 EST FATAL: could not locate required checkpoint record 2015-02-16 14:29:12 EST HINT: If you are not restoring from a backup, try removing the file E:/data/backup_label. 2015-02-16 14:29:12 EST LOG: startup process (PID 3148) exited with exit code 1 2015-02-16 14:29:12 EST LOG: aborting startup due to startup process failure I assume this is due to the fact the pg_xlog folder is empty (this is how pg_basebackup makes it in the archive) and that I haven't supplied a recovery.conf file with the restore restore_command = 'copy E:\\archivedir\\%f %p', and the archived WAL files. Now my question is: it this a correct way of moving a cluster between machines? If yes, what WAL files will I put in pg_xlog? Would I have needed to copy those that were in the old machine right after the base backup? If this is not the right way to do it, what is the best way? http://www.postgresql.org/docs/9.3/interactive/app-pgbasebackup.html -X method --xlog-method=method Includes the required transaction log files (WAL files) in the backup. This will include all transaction logs generated during the backup. If this option is specified, it is possible to start a postmaster directly in the extracted directory without the need to consult the log archive, thus making this a completely standalone backup There is more under -X, so I would read the whole section. Thanks a lot for your help, Guillaume -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] Starting new cluster from base backup
This provides part of the answer to my previous post, from the 9.4 doc (although I'm running 9.3 but I guess the second phrase in the paragraph applies to my case): Tablespaces will in plain format by default be backed up to the same path they have on the server, unless the option --tablespace-mapping is used. Without this option, running a plain format base backup on the same host as the server will not work if tablespaces are in use, because the backup would have to be written to the same directory locations as the original tablespaces. I know the -T option is not available in 9.3. Is there another way to circumvent the problem and still be able to backup using -X stream and plain format when tablespace have been created elsewhere? Thanks! 2015-02-17 9:54 GMT-05:00 Guillaume Drolet droletguilla...@gmail.com: Adrian: thanks for this information. I tried running pg_basebackup in plain format with option -X stream (pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U postgres -P) but I got the message: pg_basebackup: directory E:\Data\Database exists but is not empty I creatde a tablespace using CREATE TABLESPACE at the location mentioned in the message. According to what I read online about this, this message is issued when a tablespace was created under PGDATA. In my case, only the directory junction pointing to my tablespace (on a different drive than PGDATA) exists under PGDATA, not the tablespace itself. The only way I can run pg_basebackup with WAL files is with option -Ft and -X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes a lot of time to extract when tarred. Is there another way to approach this? Thanks. 2015-02-16 15:21 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com: On 02/16/2015 11:31 AM, Guillaume Drolet wrote: Dear listers, I want to move a cluster from one machine to another. I used pg_basebackup to create an archive and copied/extracted it over the old PGDATA location on the new machine (the server was stopped). If I start pgsql I get these messages in my log file: 2015-02-16 14:29:12 EST LOG: database system was interrupted; last known up at 2015-02-07 06:31:41 EST 2015-02-16 14:29:12 EST LOG: invalid checkpoint record 2015-02-16 14:29:12 EST FATAL: could not locate required checkpoint record 2015-02-16 14:29:12 EST HINT: If you are not restoring from a backup, try removing the file E:/data/backup_label. 2015-02-16 14:29:12 EST LOG: startup process (PID 3148) exited with exit code 1 2015-02-16 14:29:12 EST LOG: aborting startup due to startup process failure I assume this is due to the fact the pg_xlog folder is empty (this is how pg_basebackup makes it in the archive) and that I haven't supplied a recovery.conf file with the restore restore_command = 'copy E:\\archivedir\\%f %p', and the archived WAL files. Now my question is: it this a correct way of moving a cluster between machines? If yes, what WAL files will I put in pg_xlog? Would I have needed to copy those that were in the old machine right after the base backup? If this is not the right way to do it, what is the best way? http://www.postgresql.org/docs/9.3/interactive/app-pgbasebackup.html -X method --xlog-method=method Includes the required transaction log files (WAL files) in the backup. This will include all transaction logs generated during the backup. If this option is specified, it is possible to start a postmaster directly in the extracted directory without the need to consult the log archive, thus making this a completely standalone backup There is more under -X, so I would read the whole section. Thanks a lot for your help, Guillaume -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] Starting new cluster from base backup
On 02/17/2015 06:54 AM, Guillaume Drolet wrote: Adrian: thanks for this information. I tried running pg_basebackup in plain format with option -X stream (pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U postgres -P) but I got the message: pg_basebackup: directory E:\Data\Database exists but is not empty I creatde a tablespace using CREATE TABLESPACE at the location mentioned in the message. According to what I read online about this, this message is issued when a tablespace was created under PGDATA. In my case, only the directory junction pointing to my tablespace (on a different drive than PGDATA) exists under PGDATA, not the tablespace itself. I believe all pg_basebackup cares about is whether the directory or not. It does not do any sort of further investigation to determine what is in the directory. The only way I can run pg_basebackup with WAL files is with option -Ft and -X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes a lot of time to extract when tarred. Is there another way to approach this? Thanks. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Starting new cluster from base backup
On 02/17/2015 06:54 AM, Guillaume Drolet wrote: Adrian: thanks for this information. I tried running pg_basebackup in plain format with option -X stream (pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U postgres -P) but I got the message: pg_basebackup: directory E:\Data\Database exists but is not empty I creatde a tablespace using CREATE TABLESPACE at the location mentioned in the message. According to what I read online about this, this message is issued when a tablespace was created under PGDATA. In my case, only the directory junction pointing to my tablespace (on a different drive than PGDATA) exists under PGDATA, not the tablespace itself. The only way I can run pg_basebackup with WAL files is with option -Ft and -X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes a lot of time to extract when tarred. Is there another way to approach this? All I can come up with at the moment: So what is the path on the original machine and can it be replicated on the new machine, at least temporarily? I'm thinking if the path can be replicated, let pg_basebackup write to it and then create the tablespace you want and do ALTER TABLE SET TABLESPACE to move the tables. You would also need to do this for indexes. Thanks. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Starting new cluster from base backup
On 02/16/2015 11:31 AM, Guillaume Drolet wrote: Dear listers, I want to move a cluster from one machine to another. I used pg_basebackup to create an archive and copied/extracted it over the old PGDATA location on the new machine (the server was stopped). If I start pgsql I get these messages in my log file: 2015-02-16 14:29:12 EST LOG: database system was interrupted; last known up at 2015-02-07 06:31:41 EST 2015-02-16 14:29:12 EST LOG: invalid checkpoint record 2015-02-16 14:29:12 EST FATAL: could not locate required checkpoint record 2015-02-16 14:29:12 EST HINT: If you are not restoring from a backup, try removing the file E:/data/backup_label. 2015-02-16 14:29:12 EST LOG: startup process (PID 3148) exited with exit code 1 2015-02-16 14:29:12 EST LOG: aborting startup due to startup process failure I assume this is due to the fact the pg_xlog folder is empty (this is how pg_basebackup makes it in the archive) and that I haven't supplied a recovery.conf file with the restore restore_command = 'copy E:\\archivedir\\%f %p', and the archived WAL files. Now my question is: it this a correct way of moving a cluster between machines? If yes, what WAL files will I put in pg_xlog? Would I have needed to copy those that were in the old machine right after the base backup? If this is not the right way to do it, what is the best way? http://www.postgresql.org/docs/9.3/interactive/app-pgbasebackup.html -X method --xlog-method=method Includes the required transaction log files (WAL files) in the backup. This will include all transaction logs generated during the backup. If this option is specified, it is possible to start a postmaster directly in the extracted directory without the need to consult the log archive, thus making this a completely standalone backup There is more under -X, so I would read the whole section. Thanks a lot for your help, Guillaume -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Starting new cluster from base backup
Dear listers, I want to move a cluster from one machine to another. I used pg_basebackup to create an archive and copied/extracted it over the old PGDATA location on the new machine (the server was stopped). If I start pgsql I get these messages in my log file: 2015-02-16 14:29:12 EST LOG: database system was interrupted; last known up at 2015-02-07 06:31:41 EST 2015-02-16 14:29:12 EST LOG: invalid checkpoint record 2015-02-16 14:29:12 EST FATAL: could not locate required checkpoint record 2015-02-16 14:29:12 EST HINT: If you are not restoring from a backup, try removing the file E:/data/backup_label. 2015-02-16 14:29:12 EST LOG: startup process (PID 3148) exited with exit code 1 2015-02-16 14:29:12 EST LOG: aborting startup due to startup process failure I assume this is due to the fact the pg_xlog folder is empty (this is how pg_basebackup makes it in the archive) and that I haven't supplied a recovery.conf file with the restore restore_command = 'copy E:\\archivedir\\%f %p', and the archived WAL files. Now my question is: it this a correct way of moving a cluster between machines? If yes, what WAL files will I put in pg_xlog? Would I have needed to copy those that were in the old machine right after the base backup? If this is not the right way to do it, what is the best way? Thanks a lot for your help, Guillaume
