Re: [BUGS] Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.

[fburgess]

Does anyone know, what the names/location of the pg_upgrade cleanup scripts? We upgraded from 8.4.3 to 9.1.6thanks   Original Message Subject: Re: [BUGS] Excessive space allocations in Postgresql 9.1.6system files causing the file system to run out of space.From: Kevin Grittner Date: Fri, March 01, 2013 9:00 amTo: "fburg...@radiantblue.com" , AndresFreund Cc: "pgsql-bugs@postgresql.org" "fburg...@radiantblue.com"  wrote:> We did use pg_upgrade with the hard link option. We are not sure> if we ran the cleanup script.> Can we run this script now, even though its month's after we did> the upgrade?> Everything in the .../19177 directories represent data files> migrated over form postgres 8.4.3.  All new files get placed into> the .../PG_9.1_201105231/16411 directories.> The vast majority of the "orphan" files are from the> /opt/PostgreSQL/9.1/data/user_data/19177  directory.I don't have any reason to expect that you *can't* run the scriptat this point; but being a cautious person, I would do this at apoint where I was confident I could recover from a backup, and Iwould read through the scripts carefully before applying them.What you want to be really careful that you *don't* do is to modifyor truncate any of the hard-linked files, as they are quite likelyto still be just another name for the same file that is in use forproduction under the newer version.  You want to simply remove theolder directory entry pointing to the file.http://www.linfo.org/hard_link.html-- Kevin GrittnerEnterpriseDB: http://www.enterprisedb.comThe Enterprise PostgreSQL Company-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-bugs 

Re: [BUGS] Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.

[Kevin Grittner]

"fburg...@radiantblue.com"  wrote:

> Does anyone know, what the names/location of the pg_upgrade
> cleanup scripts? We upgraded from 8.4.3 to 9.1.6

Sorry I didn't answer this sooner, but I didn't know off-hand and
hoped that someone who did would jump in.  Since that didn't happen
...

The file is delete_old_cluster.sh on most platforms.  The suffix is
.bat on Windows.

It can be run anytime after the upgrade *as long as you have not
moved the new cluster to the old location*.

Thanks to Bruce Momjian for telling me in chat when I asked just now.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #7922: Error installing PostGIS

[akurzhan]

The following bug has been logged on the website:

Bug reference:  7922
Logged by:  Alex
Email address:  akurz...@gmail.com
PostgreSQL version: 9.1.2
Operating system:   Windows 7, 64-bit
Description:

I have PostgreSQL 9.1.2, 64-bit.

My installation of PostGIS-2.0.1 (64-bit) fails at the stage of creating
template database and loading PostGIS functions

The error message:
ERROR:  could not load library "c:/Program
Files/PostgreSQL/9.1/lib/postgis-2.0.dll": %1 is not a valid Win32
application.



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.

[fburgess]

Thanks Kevin & Bruce for your replies,
 
It looks like this script deletes all of the data file directories (and the data files) from the postgres 8.4.3 instance.  Since we used the "-k" option, which calls for hard links rather than copying files to the new cluster, wouldn't this be deleting the files?  If so, the DB would be corrupted beyond recovery if we run it.  Or am I missing something?
 
thanks
 
Freddie
 

 Original Message Subject: Re: [BUGS] Excessive space allocations in Postgresql 9.1.6system files causing the file system to run out of space.From: Kevin Grittner Date: Thu, March 07, 2013 8:14 amTo: "fburg...@radiantblue.com" , "pgsql-bugs@postgresql.org" "fburg...@radiantblue.com"  wrote:> Does anyone know, what the names/location of the pg_upgrade> cleanup scripts? We upgraded from 8.4.3 to 9.1.6Sorry I didn't answer this sooner, but I didn't know off-hand andhoped that someone who did would jump in.  Since that didn't happen...The file is delete_old_cluster.sh on most platforms.  The suffix is.bat on Windows.It can be run anytime after the upgrade *as long as you have notmoved the new cluster to the old location*.Thanks to Bruce Momjian for telling me in chat when I asked just now.--Kevin GrittnerEnterpriseDB: http://www.enterprisedb.comThe Enterprise PostgreSQL Company-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.

[Kevin Grittner]

"fburg...@radiantblue.com"  wrote:

> It looks like this script deletes all of the data file
> directories (and the data files) from the postgres 8.4.3
> instance. Since we used the "-k" option, which calls for hard
> links rather than copying files to the new cluster, wouldn't this
> be deleting the files?  If so, the DB would be corrupted beyond
> recovery if we run it.  Or am I missing something?

You need to read up on hard links a bit more.  Here's one source:

http://www.linfo.org/hard_link.html

In summary, a hard link allows multiple directory entries to point
to the same data location.  Until you delete the last directory
entry the file remains.

-- 
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist

[Josh Kupershmidt]

[Moving to -hackers]

On Sat, Feb 23, 2013 at 2:51 PM, Pavel Stehule  wrote:

> so
>
> * --conditional-drops replaced by --if-exists

Thanks for the fixes, I played around with the patch a bit. I was sort
of expecting this example to work (after setting up the regression
database with `make installcheck`)

  pg_dump --clean --if-exists -Fp -d regression --file=regression.sql
  createdb test
  psql -v ON_ERROR_STOP=1 --single-transaction -d test -f regression.sql

But it fails, first at:
  ...
  DROP TRIGGER IF EXISTS tsvectorupdate ON public.test_tsvector;
  ERROR:  relation "public.test_tsvector" does not exist

This seems like a shortcoming of DROP TRIGGER ... IF EXISTS, and it
looks like DROP RULE ... IF EXISTS has the same problem. I recall DROP
... IF EXISTS being fixed recently for not to error out if the schema
specified for the object does not exist, and ISTM the same arguments
could be made in favor of fixing DROP TRIGGER/TABLE ... IF EXISTS not
to error out if the table doesn't exist.

Working further through the dump of the regression database, these
also present problems for --clean --if-exists dumps:

  DROP CAST IF EXISTS (text AS public.casttesttype);
  ERROR:  type "public.casttesttype" does not exist

  DROP OPERATOR IF EXISTS public.<% (point, widget);
  ERROR:  type "widget" does not exist

  DROP FUNCTION IF EXISTS public.pt_in_widget(point, widget);
  ERROR:  type "widget" does not exist

I'm not sure whether DROP CAST/OPERATOR/FUNCTION IF EXISTS should be
more tolerant of nonexistent types, of if the mess could perhaps be
avoided by dump reordering.

Note, this usability problem affects unpatched head as well:

  pg_dump -Fc -d regression --file=regression.dump
  pg_restore --clean -1 -d regression regression.dump
  ...
  pg_restore: [archiver (db)] could not execute query: ERROR:  type
"widget" does not exist
  Command was: DROP FUNCTION public.widget_out(widget);

(The use here is a little different than the first example above, but
I would still expect this case to work.) The above problems with IF
EXISTS aren't really a problem of the patch per se, but IMO it would
be nice to straighten all the issues out together for 9.4.

> * -- additional check, available only with -c option

Cool. I think it would also be useful to check that --clean may only
be used with --format=p to avoid any confusion there. (This issue
could be addressed in a separate patch if you'd rather not lard this
patch.)

Some comments on the changes:

1. There is at least one IF EXISTS check missing from pg_dump.c, see
for example this statement from a dump of the regression database with
--if-exists:

ALTER TABLE public.nv_child_2010 DROP CONSTRAINT nv_child_2010_d_check;

2. Shouldn't pg_restore get --if-exists as well?

3.
+   printf(_("  --if-exists  don't report error if
cleaned object doesn't exist\n"));

This help output bleeds just over our de facto 80-character limit.
Also contractions seem to be avoided elsewhere. It's a little hard to
squeeze a decent explanation into one line, but perhaps:

  Use IF EXISTS when dropping objects

would be better. The sgml changes could use some wordsmithing and
grammar fixes. I could clean these up for you in a later version if
you'd like.

4. There seem to be spurious whitespace changes to the function
prototype and declaration for _printTocEntry.

That's all I've had time for so far...

Josh


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs