Re: [SQL] Migrating a Database to a new tablespace
Hello, Markus Schaber wrote: >>What is the easiest way to migrate a complete database from one >>tablespace to another? >> >>ALTER DATABASE only allows to set the default tablespace, but not >>migrating the existing database. > > Is there really no way to do this? Via some scripting, I now migrated all tables and indices, however I found no way to migrate sequences. The problem is that I cannot just move the tablespace itsself via mv/symlink/fiddling_of_systables, because the tablespace contains some other databases that are meant to remain there. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Migrating a Database to a new tablespace
Hello, Markus Schaber wrote: > What is the easiest way to migrate a complete database from one > tablespace to another? > > ALTER DATABASE only allows to set the default tablespace, but not > migrating the existing database. Is there really no way to do this? Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] "could not open file" issue
Hi, Is there any way to solve the following issue without dropping the table? select count(*) from eventplayerleaveroom; ERROR: could not access status of transaction 3164404766 DETAIL: could not open file "pg_clog/0BC9": No such file or directory Regards, P.S.: PostgreSQL server 8.1.3 -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Migrating a Database to a new tablespace
Hello, I now pulled the plug, migrated all databases via "create database ... tempate olddatabase tablespace newts" to new tablespaces, one for each database, and dropped all old databases that contained references to the tablespace. Pgadmin3 also shows that the tablespace is not referenced by anything. But I cannot drop it, I get the following message: postgres=# drop TABLESPACE foo; ERROR: tablespace "foo" is not empty It seems that the whole tablespace thing is not yet 100% waterproof, good that this did happen on a developer machine, and not on a production machine. Thanks for your patience, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "could not open file" issue
On Mon, Apr 24, 2006 at 12:17:07PM -0400, Daniel Caune wrote: > Hi, > > Is there any way to solve the following issue without dropping the > table? I doubt you'll be able to drop the table. I think you have some sort of corruption. Assuming your hardware is good, you maybe oughta take this over to -general to see if the wizards can identify your problem. (But check your hardware first.) A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] "could not open file" issue
Daniel Caune wrote: > Hi, > > Is there any way to solve the following issue without dropping the > table? > > select count(*) from eventplayerleaveroom; > ERROR: could not access status of transaction 3164404766 > DETAIL: could not open file "pg_clog/0BC9": No such file or directory Are the files in pg_clog close to the vicinity of 0BC9? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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: [SQL] "could not open file" issue
> De : Alvaro Herrera [mailto:[EMAIL PROTECTED] > > Daniel Caune wrote: > > Hi, > > > > Is there any way to solve the following issue without dropping the > > table? > > > > select count(*) from eventplayerleaveroom; > > ERROR: could not access status of transaction 3164404766 > > DETAIL: could not open file "pg_clog/0BC9": No such file or directory > > Are the files in pg_clog close to the vicinity of 0BC9? > I don't have any skill in PostgreSQL administration. However I took a look at /var/lib/postgresql/8.1/main/pg_clog and there is no file close to the vicinity of 0BC9 ; the last file in that directory is: -rw--- 1 postgres postgres 221184 2006-04-24 19:27 00C9 A bit far from 0BC9... Do you have any diagnostic? > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "could not open file" issue
Daniel Caune wrote: > > > De : Alvaro Herrera [mailto:[EMAIL PROTECTED] > > > > Daniel Caune wrote: > > > > > > select count(*) from eventplayerleaveroom; > > > ERROR: could not access status of transaction 3164404766 > > > DETAIL: could not open file "pg_clog/0BC9": No such file or directory > > > > Are the files in pg_clog close to the vicinity of 0BC9? > > I don't have any skill in PostgreSQL administration. However I took a > look at /var/lib/postgresql/8.1/main/pg_clog and there is no file > close to the vicinity of 0BC9 ; the last file in that directory is: > > -rw--- 1 postgres postgres 221184 2006-04-24 19:27 00C9 > > A bit far from 0BC9... Do you have any diagnostic? I'd say you have a corrupted table. How corrupted I don't know. You could try extracting a portion of the table, playing with LIMIT/OFFSET to find out the exact records that are corrupted. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] date array
While down loading 8.1.3, I tried the following:
update pf_inspectionsBld set insdate[0] = current_date
update pf_inspectionsBld set insdate[1] = current_date
update pf_inspectionsBld set insdate[2] = current_date
update pf_inspectionsBld set insdate[3] = current_date
update pf_inspectionsBld set insdate[4] = current_date + 1
select * from pf_inspectionsBld
A-2 |1793 |
[0:4]={04/24/2006,04/24/2006,04/24/2006,04/24/2006,04/25/2006}
yet if I try
update pr_inspectionsbld set inspdate[3] = null
then
select * from pf_inspectionsBld
A-2 |1793 |
[0:4]={04/24/2006,04/24/2006,04/24/2006,04/24/2006,04/25/2006}
i.e. no error and no change
what am I missing??
thanks
Bruno Wolff III wrote:
Please keep responses copied to the list. This both helps other people with
similar questions by getting the discussion in the archives and helps you
by letting other people see your additional questions.
On Sun, Apr 23, 2006 at 13:21:49 -0500,
"A. R. Van Hook" <[EMAIL PROTECTED]> wrote:
I am running 8.1.2, what can I do in the mean time?
You could build from source using HEAD. (This is probably not a good idea for
a production system.)
You could restructure your data model to not use arrays. (You probably
shouldn't be using them anyway. Arrays should be used for things that are
naturally arrays (e.g. vectors) and I would be surprised if this was the
case when you are storing dates.)
Also note that 8.1.3 is out and 8.1.4 will probably be out soon, so you
should be looking to upgrade in any case.
thanks
Bruno Wolff III wrote:
On Sun, Apr 23, 2006 at 07:55:54 -0500,
"A. R. Van Hook" <[EMAIL PROTECTED]> wrote:
How do you add null values to a date array?
update dattable set insDate= '{04/12/2006,null}' <-- doesn't work
That is the correct format. However, I think that only works in HEAD
(what will become 8.2 in about 6 months).
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Arthur R. Van Hook
Mayor
The City of Lake Lotawana
[EMAIL PROTECTED]
(816) 578-4704 - Home
(816) 578-4215 - City
(816) 564-0769 - Cell
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] "could not open file" issue
> De : Alvaro Herrera [mailto:[EMAIL PROTECTED] > > Daniel Caune wrote: > > > > > De : Alvaro Herrera [mailto:[EMAIL PROTECTED] > > > > > > Daniel Caune wrote: > > > > > > > > select count(*) from eventplayerleaveroom; > > > > ERROR: could not access status of transaction 3164404766 > > > > DETAIL: could not open file "pg_clog/0BC9": No such file or > directory > > > > > > Are the files in pg_clog close to the vicinity of 0BC9? > > > > I don't have any skill in PostgreSQL administration. However I took a > > look at /var/lib/postgresql/8.1/main/pg_clog and there is no file > > close to the vicinity of 0BC9 ; the last file in that directory is: > > > > -rw--- 1 postgres postgres 221184 2006-04-24 19:27 00C9 > > > > A bit far from 0BC9... Do you have any diagnostic? > > I'd say you have a corrupted table. How corrupted I don't know. You > could try extracting a portion of the table, playing with LIMIT/OFFSET > to find out the exact records that are corrupted. > Yes, I tried playing with the LIMIT clause, and LIMIT 90 is the better I can pass... :-( > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "could not open file" issue
> De : [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] De la part de Andrew Sullivan > > On Mon, Apr 24, 2006 at 12:17:07PM -0400, Daniel Caune wrote: > > Hi, > > > > Is there any way to solve the following issue without dropping the > > table? > > I doubt you'll be able to drop the table. I think you have some sort > of corruption. Assuming your hardware is good, you maybe oughta take > this over to -general to see if the wizards can identify your > problem. (But check your hardware first.) > It seems that was possible: I tried first to truncate the table (it passed), and finally I tried to drop the table (it also passed). Then I created the table. I'm not sure that it fixes my problem. I modified my fstab file so that Linux checks my file system's health on the next boot. > > -- > Andrew Sullivan | [EMAIL PROTECTED] > "The year's penultimate month" is not in truth a good way of saying > November. > --H.W. Fowler ---(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
Re: [SQL] "could not open file" issue
Daniel Caune wrote: > It seems that was possible: I tried first to truncate the table (it > passed), and finally I tried to drop the table (it also passed). Then > I created the table. I'm not sure that it fixes my problem. I > modified my fstab file so that Linux checks my file system's health on > the next boot. If you experienced memory corruption, it would be wise to run some hardware diagnosys tools, just to be sure. For example memtest86, badblocks, etc. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Migrating a Database to a new tablespace
That means that the tablespace directory isn't empty. On Mon, Apr 24, 2006 at 01:34:33PM +0200, Markus Schaber wrote: > Hello, > > I now pulled the plug, migrated all databases via "create database ... > tempate olddatabase tablespace newts" to new tablespaces, one for each > database, and dropped all old databases that contained references to the > tablespace. Pgadmin3 also shows that the tablespace is not referenced by > anything. > > But I cannot drop it, I get the following message: > > postgres=# drop TABLESPACE foo; > ERROR: tablespace "foo" is not empty > > > It seems that the whole tablespace thing is not yet 100% waterproof, > good that this did happen on a developer machine, and not on a > production machine. > > Thanks for your patience, > Markus > > -- > Markus Schaber | Logical Tracking&Tracing International AG > Dipl. Inf. | Software Development GIS > > Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- 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 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
