Re: [DOCS] Online Backups: Minor Caveat, Major Addition?
On Mon, Mar 20, 2006 at 09:54:53AM -0600, Thomas F. O'Connell wrote: > 23.3.3.1 - Inline (or In-place) Recovery > 23.3.3.2 - Remote Recovery or Recovery into a New Cluster > 23.3.3.3 - Continuous Recovery > > The "Inline Recovery" section would just be the existing 23.3.3 > repurposed. > > The "Remote/New Cluster Recovery" section would be an edited version > of the existing 23.3.3 to eliminate step1 and include the note > suggested above. > > The "Continuous Recovery" section would include details of how to > continuously apply WAL files to a separate cluster in order to have a > true hot standby system. > > Thoughts? > > I'd be happy to draft 23.3.3.2. I'll have to figure out how to > implement Simon Riggs's suggestion of a wait-for-files > recover_command including a way to interrupt in the event of a need > for actual failover-style recovery before I could draft 23.3.3.3, > though. BTW, when it comes to continuous recovery you should have a look at http://pgfoundry.org/projects/pgpitrha/ -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[DOCS] ALTER TABLE x ALTER COLUMN y TYPE z
On this page: http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html there is this user comment: To change the data type of a column, do this: BEGIN; ALTER TABLE tab ADD COLUMN new_col new_data_type; UPDATE tab SET new_col = CAST(old_col AS new_data_type); ALTER TABLE tab RENAME old_col TO temp_name; ALTER TABLE tab RENAME new_col TO old_col; ALTER TABLE tab DROP COLUMN temp_name; COMMIT; You might then want to do VACUUM FULL tab to reclaim the disk space used by the expired rows. The 8.1 release (and the 8.0 release) support the same functionality with a single line: ALTER TABLE tab ALTER COLUMN old_col TYPE new_data_type; I think the user comment should be removed, unless there is some benefit to using the multi-step process. If there is some benefit, I think it should be described, so that users know when to use it instead of the simpler technique. -Kevin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [DOCS] ALTER TABLE x ALTER COLUMN y TYPE z
"Kevin Grittner" <[EMAIL PROTECTED]> writes: > On this page: > http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html > ... > I think the user comment should be removed, unless there is some > benefit to using the multi-step process. There isn't, and I agree it should go. The comment seems to have been attached to both 7.4 and 8.1 at the same time --- it is useful for 7.4, but not later versions. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [DOCS] ALTER TABLE x ALTER COLUMN y TYPE z
On Wednesday 22 March 2006 12:58, Kevin Grittner wrote:
> On this page:
>
> http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html
>
> there is this user comment:
>
>
>
> To change the data type of a column, do this:
>
> BEGIN;
> ALTER TABLE tab ADD COLUMN new_col new_data_type;
> UPDATE tab SET new_col = CAST(old_col AS new_data_type);
> ALTER TABLE tab RENAME old_col TO temp_name;
> ALTER TABLE tab RENAME new_col TO old_col;
> ALTER TABLE tab DROP COLUMN temp_name;
> COMMIT;
>
> You might then want to do VACUUM FULL tab to reclaim the disk space
> used by the expired rows.
>
>
>
> The 8.1 release (and the 8.0 release) support the same functionality
> with a single line:
>
> ALTER TABLE tab ALTER COLUMN old_col TYPE new_data_type;
>
> I think the user comment should be removed, unless there is some
> benefit to using the multi-step process. If there is some benefit, I
> think it should be described, so that users know when to use it instead
> of the simpler technique.
>
I believe Tom's comments in this email apply similarly here.
http://archives.postgresql.org/pgsql-general/2006-03/msg00891.php
Feel free to submit an additional doc comment.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [DOCS] ALTER TABLE x ALTER COLUMN y TYPE z
>>> On Wed, Mar 22, 2006 at 1:00 pm, in message <[EMAIL PROTECTED]>, Robert Treat <[EMAIL PROTECTED]> wrote: > > I believe Tom's comments in this email apply similarly here. > http://archives.postgresql.org/pgsql- general/2006- 03/msg00891.php The user comment's recommended technique includes this line: ALTER TABLE tab DROP COLUMN temp_name; Would this cause a table rewrite? (Not a rhetorical question. I really don't know.) > Feel free to submit an additional doc comment. I did, but it was rejected -- presumably because it included a question. Once I have a better handle on the issue, if it seems like it needs it, I'll try again. -Kevin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] ALTER TABLE x ALTER COLUMN y TYPE z
"Kevin Grittner" <[EMAIL PROTECTED]> writes: > The user comment's recommended technique includes this line: > ALTER TABLE tab DROP COLUMN temp_name; > Would this cause a table rewrite? (Not a rhetorical question. I > really don't know.) No, it wouldn't. The UPDATE is the only part that modifies the table contents. However, the arguments Robert pointed to had to do with concurrent access to the table, and somehow I am not seeing the use-case for ALTER COLUMN TYPE on a table that's being actively used by other clients. It seems unlikely that you could do that without needing to also update your client software. I'd be a tad worried about stale-cached-plan problems too, in current PG releases. Finally, since the first ALTER will take an exclusive lock that won't be released until COMMIT, this approach doesn't avoid the problem of holding exclusive lock for a long time. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] ALTER TABLE x ALTER COLUMN y TYPE z
On Wednesday 22 March 2006 15:14, Tom Lane wrote:
> However, the arguments Robert pointed to had to do with concurrent
> access to the table, and somehow I am not seeing the use-case for ALTER
> COLUMN TYPE on a table that's being actively used by other clients.
> It seems unlikely that you could do that without needing to also update
> your client software.
Altering between char and text or some such? Or something like INET to TEXT.
(Not that it matters for the things being discussed here, but I think there
are cases)
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [DOCS] Online Backups: Minor Caveat, Major Addition?
On Mar 20, 2006, at 5:21 PM, Scott Marlowe wrote: On Mon, 2006-03-20 at 17:12, Tom Lane wrote: "Thomas F. O'Connell" <[EMAIL PROTECTED]> writes: What about the larger suggested change of breaking that section into three more granular subsections? I could see commentary being slightly more helpful for each. No particular opinion from here. Someone (was it Scott Marlowe?) recently volunteered to draft a complete restructuring of the admin docs --- so it would probably be better to think about this as part of that effort rather than a standalone change. That was me, and I'm working on it right now, in the background. If anyone has any input, I'd be glad to hear it. I'm still getting docbooks setup on my laptop and such... Well, my input is to break 23.3.3 out into 3 practical scenarios: 1) recovery (same-server) 2) one-time (or occasional) remote backup 3) continuous recovery I just noticed another caveat for remote recovery: disable archive_command in the postgresql.conf from the filesystem-level backup. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 3004 B Poston Avenue Nashville, TN 37203-1314 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
