Re: [GENERAL] Missing objects in pg_dump

[Adrian Klaver]

On 08/15/2014 01:52 PM, AlexK wrote:

To deploy my changes, I am using apgdiff. For that, I am invoking the
following command:

pg_dump --host=my_dev_server --username=myself --no-password --schema-only
--file=C:\Temp\mydb_old.sql  my_test_db

Two objects are present in my test database, but not in the dump file. I can
invoke them from PgAdmin without any problem. This is PostgreSql 9.3 running
on Windows 7.

What am I missing?


What are the missing objects?




--
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] Missing objects in pg_dump

[AlexK]

To deploy my changes, I am using apgdiff. For that, I am invoking the
following command:

pg_dump --host=my_dev_server --username=myself --no-password --schema-only
--file=C:\Temp\mydb_old.sql  my_test_db

Two objects are present in my test database, but not in the dump file. I can
invoke them from PgAdmin without any problem. This is PostgreSql 9.3 running
on Windows 7.

What am I missing?



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Missing-objects-in-pg-dump-tp5815010.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Archiving skipped an xlog?

[hubert depesz lubaczewski]

Hi,
we have weird situation today.

Pg 9.1.13, on ubuntu, on AWS.

At ~ 7am one of db servers stopped responding, and got restarted using AWS
console.

After reboot everything seemed to work fine.

But later on I found that one of the segments was not archived.

Segment ...7 was written (mtime of file in pg_xlog) at 6:51 (more or less)
Segment ...8 was written at 6:57
Segment ...9 was written at 7:13

then there were more.

What's interesting - in archive_status, i see .done files for all xlogs,
except for ...8. In logs the *8 xlog is not mentioned (I was looking for
error message or something).

The xlog didn't make it to destination or archiving, but instead of being
retried after reboot - pg moved to *9, and then continued.

I manually archived *8, and everythinh was fine afterwards, but I'm puzzled
on how could it happen that an xlog gets skipped? Even in case of
force-reboot.

Regards,

depesz

Re: [GENERAL] Next steps in debugging database storage problems?

[Terry Schmitt]

I can't offer a whole lot of detail at this point, but I experienced a
pretty bad caching issue about 2 years ago using XFS.

We were migrating a 1TB+ Oracle database to EDB's Advanced server 9.1
(Close enough for this discussion). I normally use ext4, but decided to try
XFS for this build-out.
This was a Redhat 6.x system using NetApp SAN for storage. We extensively
leverage FlexClones for creating production "read-only" instances as well
as our development and testing environments. We take the snapshots of the
running database storage and create FlexClones. The newly cloned database
does a quick recovery on startup and away it goes. This has worked
perfectly when using ext4 for years.

The problem I experienced with XFS, was when I started up the new clone for
the first time. We would start getting various block read errors when
accessing tables and indexes and knew the database was totally unreliable
at this point.It was super painful troubleshooting as I could recreate the
problem consistently, but it took a couple days of loading data and some
creative scripts to recreate.

NetApp snapshots are consistent and reliable. It was clearly obvious that
the data on disk did not match the data cached by the OS and/or XFS. We
worked with Redhat, but never arrived at a solution. I finally gave up and
switched back to ext4 and the problem went away.

T




On Fri, Aug 15, 2014 at 12:23 AM, Jacob Bunk Nielsen  wrote:

> Hi
>
> On the 1st of July 2014 Jacob Bunk Nielsen  wrote:
>
> > We have a PostgreSQL 9.3.4 running in an LXC container on Debian
> > Wheezy on a Linux 3.10.43 kernel on a Dell R620 server. Data are
> > stored on a XFS file system. We are seeing problems such as:
> >
> > unexpected data beyond EOF in block 2 of relation
> base/805208133/1238511128
> >
> > and
> >
> > could not read block 5 in file "base/805208348/1259338118": read only
> > 0 of 8192 bytes
> >
> > This seems to occur every few days after the server has been up for
> > 30-40 days. If we reboot the server it'll be another 30-40 days before
> > we see any problems again.
> >
> > The server has been running fine on a Dell R710 for a long time, and was
> > upgraded to a Dell R620 last year, when the problems started. We have
> > tried switching to a different Dell R620, but that did not make a
> > difference. We've seen this with kernels 3.2, 3.4 and 3.10.
>
> This time it took 45 days before this happened:
>
> LOG:  unexpected EOF on standby connection
> ERROR:  unexpected data beyond EOF in block 140 of relation
> base/805208885/805209852
> HINT:  This has been seen to occur with buggy kernels; consider updating
> your system.
>
> It always happens with small tables with lots of inserts and deletes.
> From previous experience we know that it's now going to happen again in
> a few days, so we'll probably try to schedule a reboot to give us
> another 30-40 days.
>
> Is anyone else seeing problems with PostgreSQL on XFS filesystems?
>
> Any hints on how to debug what goes wrong here would be still be greatly
> appreciated.
>
> > We have multiple other PostgreSQL servers running in a similar setup
> > without causing any problems, but this server is probably the busiest of
> > our PostgreSQL servers.
>
> This is still the case.
>
> Best regards
>
> Jacob
>
>
>
> --
> 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] Upgrading 9.0 to 9.3 - permission denied to pg_upgrade_utility.log

[Russell Keane]

-Original Message-
From: Bruce Momjian [mailto:br...@momjian.us] 
Sent: 15 August 2014 17:06
To: Russell Keane
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Upgrading 9.0 to 9.3 - permission denied to 
pg_upgrade_utility.log

On Tue, Aug 12, 2014 at 10:59:16AM +0100, Russell Keane wrote:
> > The upgrade works all the way up until the final hurdle where, in 
> > the pg_upgrade_utility.log the following statement appears:
> > 
> > command: ""C:\Program Files (x86)\PostgreSQL\9.3\bin/initdb" 
> > --sync-only "D:\ PostgreSQL\9.3\Data" >> "pg_upgrade_utility.log" 2>&1"
> > 
> > syncing data to disk ... initdb: could not open file 
> > "D:/PostgreSQL/9.3/Data/
> > pg_upgrade_utility.log": Permission denied
> 
> Uh, it would appear you are running pg_upgrade from _inside_ the 9.3 data 
> directory.  That should work, but it would probably be better to run it in 
> another directory where you also have write permission.  I think the problem 
> is that initdb --sync-only is syncing those files to disk as you are writing 
> to the log file.
> 
> We have had Windows problems of two processes writing to the same file, but 
> that is usually a different error message, e.g.
> 
>  * For some reason, Windows issues a file-in-use error if we write data to
>  * the log file from a non-primary thread just before we create a
>  * subprocess that also writes to the same log file.  One fix is to sleep
>  * for 100ms.  A cleaner fix is to write to the log file _after_ the
>  * subprocess has completed, so we do this only when writing from a
>  * non-primary thread.  fflush(), running system() twice, and pre-creating
>  * the file do not see to help.
> 
> I think that returns a "share violation" error.

I have applied the attached patch for 9.5 which will generate a clearer error 
in this case, attached.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +




Thank you once again.

Regards,

Russell.


-- 
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] Upgrading 9.0 to 9.3 - permission denied to pg_upgrade_utility.log

[Bruce Momjian]

On Tue, Aug 12, 2014 at 10:59:16AM +0100, Russell Keane wrote:
> > The upgrade works all the way up until the final hurdle where, in the 
> > pg_upgrade_utility.log the following statement appears:
> > 
> > command: ""C:\Program Files (x86)\PostgreSQL\9.3\bin/initdb" 
> > --sync-only "D:\ PostgreSQL\9.3\Data" >> "pg_upgrade_utility.log" 2>&1"
> > 
> > syncing data to disk ... initdb: could not open file 
> > "D:/PostgreSQL/9.3/Data/
> > pg_upgrade_utility.log": Permission denied
> 
> Uh, it would appear you are running pg_upgrade from _inside_ the 9.3 data 
> directory.  That should work, but it would probably be better to run it in 
> another directory where you also have write permission.  I think the problem 
> is that initdb --sync-only is syncing those files to disk as you are writing 
> to the log file.
> 
> We have had Windows problems of two processes writing to the same file, but 
> that is usually a different error message, e.g.
> 
>  * For some reason, Windows issues a file-in-use error if we write data to
>  * the log file from a non-primary thread just before we create a
>  * subprocess that also writes to the same log file.  One fix is to sleep
>  * for 100ms.  A cleaner fix is to write to the log file _after_ the
>  * subprocess has completed, so we do this only when writing from a
>  * non-primary thread.  fflush(), running system() twice, and pre-creating
>  * the file do not see to help.
> 
> I think that returns a "share violation" error.

I have applied the attached patch for 9.5 which will generate a clearer
error in this case, attached.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/contrib/pg_upgrade/option.c b/contrib/pg_upgrade/option.c
new file mode 100644
index e0a3c6d..2e7c347
*** a/contrib/pg_upgrade/option.c
--- b/contrib/pg_upgrade/option.c
*** parseCommandLine(int argc, char *argv[])
*** 229,234 
--- 229,254 
  			 "PGDATAOLD", "-d", "old cluster data resides");
  	check_required_directory(&new_cluster.pgdata, &new_cluster.pgconfig,
  			 "PGDATANEW", "-D", "new cluster data resides");
+ 
+ #ifndef WIN32
+ 	/*
+ 	 * On Windows, initdb --sync-only will fail with a "Permission denied"
+ 	 * error on file pg_upgrade_utility.log if pg_upgrade is run inside
+ 	 * the new cluster directory, so we do a check here.
+ 	 */
+ 	{
+ 		char	cwd[MAXPGPATH], new_cluster_pgdata[MAXPGPATH];
+ 
+ 		strlcpy(new_cluster_pgdata, new_cluster.pgdata, MAXPGPATH);
+ 		canonicalize_path(new_cluster_pgdata);
+ 		
+ 		if (!getcwd(cwd, MAXPGPATH))
+ 			pg_fatal("cannot find current directory\n");
+ 		canonicalize_path(cwd);
+ 		if (path_is_prefix_of_path(new_cluster_pgdata, cwd))
+ 			pg_fatal("cannot run pg_upgrade from inside the new cluster data directory on Windows\n");
+ 	}
+ #endif
  }
  
  

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

[GENERAL] Support functions for GiST index on citext

[Chris Hanks]

Hi -

I have a table with a citext[] column, and I'm trying to write a
uniqueness constraint for the array values. That is, two rows with
{one,two} and {two,three} would conflict. Since it's citext, also
{one,two} and {TWO, THREE} should conflict too.

My first thought was to make a unique index using GIN, but that
doesn't seem to be an option. Someone in IRC suggested an exclusion
constraint, but it looks like the citext extension doesn't include any
support for GiST operators.

So now I'm trying to write my own GiSt-citext operator class to
accomplish this. So far I have:

CREATE OPERATOR CLASS _citext_ops DEFAULT
  FOR TYPE _citext USING gist AS
  OPERATOR 3 &&(anyarray, anyarray),
  OPERATOR 7 @>(anyarray, anyarray),
  OPERATOR 8 <@(anyarray, anyarray),
  OPERATOR 6 =(anyarray, anyarray),
  FUNCTION 7 citext_eq(citext, citext),
  STORAGE citext;

I know I need more functions, but I'm not sure what they should be, or
if its even possible to do this in raw SQL (I'm hosted on Heroku so I
don't have the freedom to compile my own functions in C, even if I
knew it).

Can anyone guide me on how to finish this, or maybe on a simpler way
to accomplish the same thing?

Thanks!
Chris


-- 
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] list of index

[FarjadFarid(ChkNet)]

 

Thanks Melvin, That worked for me. Great.

 

 

 

From: Melvin Davidson
[mailto:melvin6...@yahoo.com] 
Sent: 15 August 2014 15:46
To: farjad.fa...@checknetworks.com;
pgsql-general@postgresql.org
Subject: Re: [GENERAL] list of index 

 

 

>On a Postgresql database i like to obtain (using
an sql 

>statement) the list of all user defined indexes
and their 

>details specially the column "order by" sort
order. e.g. 

>ASC or DESC. Any help would be much appreciated.


 

either of the following queries should help:

 

SELECT pg_get_indexdef(idx.indexrelid) || ';'
  FROM pg_stat_all_indexes i
  JOIN pg_class c ON (c.oid = i.relid)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_index idx ON (idx.indexrelid =
i.indexrelid )
 WHERE NOT idx.indisprimary
   AND NOT idx.indisunique
   AND i.relname NOT LIKE 'pg_%'
   AND i.idx_scan = 0
   ORDER BY n.nspname,
  i.relname;


SELECT n.nspname as schema,
   i.relname as table,
   i.indexrelname as index,
   i.idx_scan,
   i.idx_tup_read,
   i.idx_tup_fetch,
 
pg_size_pretty(pg_relation_size(quote_ident(n.nspn
ame) || '.' || quote_ident(i.relname))) AS
table_size, 
 
pg_size_pretty(pg_relation_size(quote_ident(n.nspn
ame) || '.' || quote_ident(i.indexrelname))) AS
index_size,
   pg_get_indexdef(idx.indexrelid) as
idx_definition
  FROM pg_stat_all_indexes i
  JOIN pg_class c ON (c.oid = i.relid)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_index idx ON (idx.indexrelid =
i.indexrelid )
 WHERE n.nspname NOT LIKE 'pg_%'
 ORDER BY 1, 2, 3;

 

Melvin Davidson 
Cell 720-320-0155 

I reserve the right to fantasize.  Whether or not
you 
wish to share my fantasy is entirely up to you.
 

www.youtube.com/unusedhero

Folk Alley - All Folk - 24 Hours a day 
www.folkalley.com

Re: [GENERAL] list of index

[FarjadFarid(ChkNet)]

 

Hi ,

 

I have done that. The important point is obtaining the “sort order” of each 
column in the index. Specially multi column index. 

 

Are there no views? 

How does the engine handle sort order of a multi column index?!!

>From what I have seen it is rather complicated in postgres. 

 

Any suggestion would be very welcome.  Many Thanks. 

 

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Szymon Guz
Sent: 15 August 2014 15:32
To: farjad.fa...@checknetworks.com
Cc: PostgreSQL
Subject: Re: [GENERAL] list of index

 

On 15 August 2014 16:23, FarjadFarid(ChkNet)  
wrote:

Hi , 

 

On a Postgresql database i like to obtain (using an sql statement) the list of 
all user defined indexes and their details specially the column "order by" sort 
order. e.g. ASC or DESC. Any help would be much appreciated.  

 

 

 

Hi,
take a look at: select * from pg_indexes;

Re: [GENERAL] list of index

[Szymon Guz]

On 15 August 2014 16:23, FarjadFarid(ChkNet)  wrote:

> Hi ,
>
>
>
> On a Postgresql database i like to obtain (using an sql statement) the
> list of all user defined indexes and their details specially the column
> "order by" sort order. e.g. ASC or DESC. Any help would be much
> appreciated.
>
>
>
>
>

Hi,
take a look at: select * from pg_indexes;

[GENERAL] list of index

[FarjadFarid(ChkNet)]

Hi , 

 

On a Postgresql database i like to obtain (using
an sql statement) the list of all user defined
indexes and their details specially the column
"order by" sort order. e.g. ASC or DESC. Any help
would be much appreciated.  

 

 

Re: [GENERAL] Next steps in debugging database storage problems?

[Jacob Bunk Nielsen]

Hi

On the 1st of July 2014 Jacob Bunk Nielsen  wrote:

> We have a PostgreSQL 9.3.4 running in an LXC container on Debian
> Wheezy on a Linux 3.10.43 kernel on a Dell R620 server. Data are
> stored on a XFS file system. We are seeing problems such as:
>
> unexpected data beyond EOF in block 2 of relation base/805208133/1238511128
>
> and
>
> could not read block 5 in file "base/805208348/1259338118": read only
> 0 of 8192 bytes
>
> This seems to occur every few days after the server has been up for
> 30-40 days. If we reboot the server it'll be another 30-40 days before
> we see any problems again.
>
> The server has been running fine on a Dell R710 for a long time, and was
> upgraded to a Dell R620 last year, when the problems started. We have
> tried switching to a different Dell R620, but that did not make a
> difference. We've seen this with kernels 3.2, 3.4 and 3.10.

This time it took 45 days before this happened:

LOG:  unexpected EOF on standby connection
ERROR:  unexpected data beyond EOF in block 140 of relation 
base/805208885/805209852
HINT:  This has been seen to occur with buggy kernels; consider updating your 
system.

It always happens with small tables with lots of inserts and deletes.
>From previous experience we know that it's now going to happen again in
a few days, so we'll probably try to schedule a reboot to give us
another 30-40 days.

Is anyone else seeing problems with PostgreSQL on XFS filesystems?

Any hints on how to debug what goes wrong here would be still be greatly
appreciated.

> We have multiple other PostgreSQL servers running in a similar setup
> without causing any problems, but this server is probably the busiest of
> our PostgreSQL servers.

This is still the case.

Best regards

Jacob



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