[GENERAL] corrupt data from invalid recovery

[CS_DBA]

We are trying to help a client, one of their databases was being backed 
up via snapshots without running a pg_start_backup or pg_stop _backup
Recently they had an issue and they recovered from one of these snapshot 
backups, which are now producing errors such as :


Invalid page header in block XX of relation “tablename”


they have attempted to run reindexes and vacuum full's, even backing up 
the restored database but all ultimately ending in relation/page 
issues... Any suggestions on how we might fix this for them?


Thanks in advance




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

[GENERAL] JSON Indexes

[CS_DBA]

Hi All;

We're struggling to get the planner to use a json index.

I have this table :

  Table public.mytest

 Column |   Type| Modifiers

+---+-

 id | bigint| not null default 
nextval('events_id_seq'::regclass)


 task  | json  |


I added a PK constraint on the id column and created this json index:

create index mytest_json_col_idx on mytest ((task-'name'));

However the planner never uses the index...


EXPLAIN SELECT (mytest.task-'name') as name,

COUNT((mytest.task-'name')) AS task_count

FROM mytest

GROUP BY (mytest.task-'name')

ORDER BY 2  DESC;


   QUERY PLAN

-

 Sort  (cost=155097.84..155098.34 rows=200 width=32)

   Sort Key: (count(((task - 'name'::text

   -  HashAggregate  (cost=155087.70..155090.20 rows=200 width=32)

 -  Seq Scan on mytab  (cost=0.00..149796.94 rows=705435 width=32)


Am I missing something?


Thanks in advance...







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

[GENERAL] HOT standby with ONLY WAL shipping?

[CS_DBA]

Hi All;

We would like to setup a hot standby server with a forced delay.
Is it possible to setup a hot standby based ONLY on WAL shipping and NOT 
use streaming replication?


Thanks in advance


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

[GENERAL] Replication failover on windows

[CS_DBA]

Hi all;

we have a client running PostgreSQL on windows, and they want to run 
streaming replication with some sort of failover.


We have streaming replication in place, we thought we could use 
pgbouncer and in the case of the master being down our heartbeat script 
would reload the pgbouncer configs however pgbouncer on windows seems to 
never time out connections even when we set it to timeout. So this 
solution is probably a no go.


We are currently looking at IP aliases but it's windows so I have little 
confidence that it will be simple or stable.


Has anyone else deployed a heartbeat/failover solution on windows 
platforms? Any suggestions per an approach that would be seamless to the 
applications?



Thanks in advance





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

[GENERAL] HOT standby on windows not working

[CS_DBA]

Hi All;

We're setting up a HOT standby on Windows 2000 server and PostgreSQL 9.2

We do this:

1) stop both servers

2) copy the master data directory to the slave

3) edit the master postgresql.conf file as follows:
Modify the following listen_address = ‘*’
wal_level = hot_standby
max_wal_senders = 3


4) edit the master pg_hba.conf file and add this line:
host replication all 192.168.91.136/32 trust

where 192.168.91.136 is the IP of the slave


5) edit the postgresql.conf file on the slave as follows:
hot_standby = on


6) create a recovery.conf with the following contents:
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.91.165’

where 192.168.91.165 is the IP of the master

7) start the standby

8) start the master


The standby simply comes online, almost like it is ignoring the 
recovery.conf file.

Here's a tail of the log from the slave when we start it:

2014-04-10 15:45:24 MDT [3756]: [1-1] LOG: database system was 
interrupted; last known up at 2014-04-10 16:36:17 MDT
2014-04-10 15:45:24 MDT [3756]: [2-1] LOG: database system was not 
properly shut down; automatic recovery in progress
2014-04-10 15:45:24 MDT [3756]: [3-1] LOG: record with zero length at 
0/880

2014-04-10 15:45:24 MDT [3756]: [4-1] LOG: redo is not required
2014-04-10 15:45:24 MDT [3592]: [1-1] LOG: database system is ready to 
accept connections

2014-04-10 15:45:24 MDT [3572]: [1-1] LOG: autovacuum launcher started



Any thoughts why this is not working?

I've also tried this approach:


1) Master postgresql.conf file
Modify the following settings:
listen_address = ‘*’
wal_level = hot_standby
max_wal_senders = 3


2) Modify Master pg_hba.conf file:
hostssl replication al 192.168.91.136/32 trust

3) RESTART MASTER DATABASE

4) Slave postgresql.conf file
hot_standby = on

5) Create a recovery.conf file on the slave as follows:
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.91.165’


6) Execute a ‘pg_start_backup’ on the master.
SELECT pg_start_backup (‘date’, true);

7) Copy the data files to the standby.
a) Copied the master data directory to the slave desktop
b) removed postgresql.conf and pg_hba.conf from the data directory (the 
copy on the slave desktop)
c) removed the pg_xlog dir from the data directory (the copy on the 
slave desktop)



8) Execute a ‘pg_stop_backup’ on the master.
SELECT pg_stop_backup ();

9) Copy the Write Ahead Log (WAL) files (the data/pg_xlog directory) 
from the master to the standby


10) start the standby database

We get the same behavior (i.e. the slave comes fully online, not just 
into recovery / standby mode) no matter which approach we use...


Thoughts?

Thanks in advance...






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

[GENERAL] encrypting data stored in PostgreSQL

[CS_DBA]

Hi All;

We have a client with this requirement:

At rest data must be encrypted with a unique client key

Any thoughts on how to pull this off for PostgreSQL stored data?


Thanks in advance



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

[GENERAL] check constraint question

[CS_DBA]

Hi All

we have a table like so:


customer (
cust_id integer not null primary key,
cust_group_id  integer not null,
group_account_idinteger not null,
cust_name   varchar not null,

...
)

we want to force the cust_group_id to be unique across all 
group_account_id's but not necessarily across the entire table


I assume the best approach would be a check constraint yes? Will this be 
excessively poor per performance if the table gets big?


Thoughts?

Thanks in advance





--
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] check constraint question

[CS_DBA]

On 04/08/2014 02:58 PM, Rob Sargent wrote:

On 04/08/2014 02:51 PM, CS_DBA wrote:

Hi All

we have a table like so:


customer (
cust_id integer not null primary key,
cust_group_id  integer not null,
group_account_idinteger not null,
cust_name   varchar not null,

...
)

we want to force the cust_group_id to be unique across all 
group_account_id's but not necessarily across the entire table


I assume the best approach would be a check constraint yes? Will this 
be excessively poor per performance if the table gets big?


Thoughts?

Thanks in advance





A unique index on cust_group_id and group_account_id doesn't do it for 
you?



oh right! duh!   It's been one of those days

Re: [GENERAL] check constraint question

[CS_DBA]

On 04/08/2014 03:17 PM, Rob Sargent wrote:

On 04/08/2014 03:09 PM, CS_DBA wrote:


On 04/08/2014 02:58 PM, Rob Sargent wrote:

On 04/08/2014 02:51 PM, CS_DBA wrote:

Hi All

we have a table like so:


customer (
cust_id integer not null primary key,
cust_group_id  integer not null,
group_account_idinteger not null,
cust_name   varchar not null,

...
)

we want to force the cust_group_id to be unique across all 
group_account_id's but not necessarily across the entire table


I assume the best approach would be a check constraint yes? Will 
this be excessively poor per performance if the table gets big?


Thoughts?

Thanks in advance





A unique index on cust_group_id and group_account_id doesn't do it 
for you?



oh right! duh!   It's been one of those days






Which column goes first depends on your lookup expectations.


Thanks!


Here's another one:


customer (
cust_id integer not null primary key,
cust_group_id  integer not null,
group_account_idinteger not null,
cust_name   varchar not null,
cust_template_id  integer,
...
)

If cust_template_id IS NOT NULL then it must reference a valid cust_id

Check constraint?

Re: [GENERAL] check constraint question

[CS_DBA]

On 04/08/2014 03:31 PM, Rob Sargent wrote:

On 04/08/2014 03:26 PM, CS_DBA wrote:


On 04/08/2014 03:17 PM, Rob Sargent wrote:

On 04/08/2014 03:09 PM, CS_DBA wrote:


On 04/08/2014 02:58 PM, Rob Sargent wrote:

On 04/08/2014 02:51 PM, CS_DBA wrote:

Hi All

we have a table like so:


customer (
cust_id integer not null primary key,
cust_group_id  integer not null,
group_account_idinteger not null,
cust_name   varchar not null,

...
)

we want to force the cust_group_id to be unique across all 
group_account_id's but not necessarily across the entire table


I assume the best approach would be a check constraint yes? Will 
this be excessively poor per performance if the table gets big?


Thoughts?

Thanks in advance





A unique index on cust_group_id and group_account_id doesn't do it 
for you?



oh right! duh!   It's been one of those days






Which column goes first depends on your lookup expectations.


Thanks!


Here's another one:


customer (
cust_id integer not null primary key,
cust_group_id  integer not null,
group_account_idinteger not null,
cust_name   varchar not null,
cust_template_id  integer,
...
)

If cust_template_id IS NOT NULL then it must reference a valid cust_id

Check constraint?


Nope. Useless column :).  You already have cust_id so cust_template_id 
is either null or already known.


Actually its a goofy design in the web app... users can enter the 
template_id on the fly and if they do we want to enforce the fact that 
it's a valid cust_id (meaning any existing cust_id can be used as a 
template but made up template ID's - meaning an id that does not match 
an existing cust_id should be disallowed)



Thoughts?

Re: [GENERAL] check constraint question

[CS_DBA]

On 04/08/2014 03:41 PM, Rob Sargent wrote:

On 04/08/2014 03:36 PM, CS_DBA wrote:


On 04/08/2014 03:31 PM, Rob Sargent wrote:

On 04/08/2014 03:26 PM, CS_DBA wrote:


On 04/08/2014 03:17 PM, Rob Sargent wrote:

On 04/08/2014 03:09 PM, CS_DBA wrote:


On 04/08/2014 02:58 PM, Rob Sargent wrote:

On 04/08/2014 02:51 PM, CS_DBA wrote:

Hi All

we have a table like so:


customer (
cust_id integer not null primary key,
cust_group_id  integer not null,
group_account_idinteger not null,
cust_name   varchar not null,

...
)

we want to force the cust_group_id to be unique across all 
group_account_id's but not necessarily across the entire table


I assume the best approach would be a check constraint yes? 
Will this be excessively poor per performance if the table gets 
big?


Thoughts?

Thanks in advance





A unique index on cust_group_id and group_account_id doesn't do 
it for you?



oh right! duh!   It's been one of those days






Which column goes first depends on your lookup expectations.


Thanks!


Here's another one:


customer (
cust_id integer not null primary key,
cust_group_id  integer not null,
group_account_idinteger not null,
cust_name   varchar not null,
cust_template_id  integer,
...
)

If cust_template_id IS NOT NULL then it must reference a valid cust_id

Check constraint?


Nope. Useless column :).  You already have cust_id so 
cust_template_id is either null or already known.


Actually its a goofy design in the web app... users can enter the 
template_id on the fly and if they do we want to enforce the fact 
that it's a valid cust_id (meaning any existing cust_id can be used 
as a template but made up template ID's - meaning an id that does not 
match an existing cust_id should be disallowed)



Thoughts?



Really goofy. They could type in any valid cust_id, theirs or not theirs.
What are you after with template_id.  How would your app use it. Why 
would user fill it in?


Not sure yet (new client)... for now they simply want to force the 
template column to be a valid cust_id, if it is not null...  later I'll 
be digging into their design and pushing them to make some db 
architecture changes...

Re: [GENERAL] check constraint question

[CS_DBA]

On 04/08/2014 04:08 PM, Rob Sargent wrote:

On 04/08/2014 03:53 PM, CS_DBA wrote:


On 04/08/2014 03:41 PM, Rob Sargent wrote:

On 04/08/2014 03:36 PM, CS_DBA wrote:


On 04/08/2014 03:31 PM, Rob Sargent wrote:

On 04/08/2014 03:26 PM, CS_DBA wrote:


On 04/08/2014 03:17 PM, Rob Sargent wrote:

On 04/08/2014 03:09 PM, CS_DBA wrote:


On 04/08/2014 02:58 PM, Rob Sargent wrote:

On 04/08/2014 02:51 PM, CS_DBA wrote:

Hi All

we have a table like so:


customer (
cust_id integer not null primary key,
cust_group_id  integer not null,
group_account_idinteger not null,
cust_name   varchar not null,

...
)

we want to force the cust_group_id to be unique across all 
group_account_id's but not necessarily across the entire table


I assume the best approach would be a check constraint yes? 
Will this be excessively poor per performance if the table 
gets big?


Thoughts?

Thanks in advance





A unique index on cust_group_id and group_account_id doesn't 
do it for you?



oh right! duh!   It's been one of those days






Which column goes first depends on your lookup expectations.


Thanks!


Here's another one:


customer (
cust_id integer not null primary key,
cust_group_id  integer not null,
group_account_idinteger not null,
cust_name   varchar not null,
cust_template_id  integer,
...
)

If cust_template_id IS NOT NULL then it must reference a valid 
cust_id


Check constraint?


Nope. Useless column :).  You already have cust_id so 
cust_template_id is either null or already known.


Actually its a goofy design in the web app... users can enter the 
template_id on the fly and if they do we want to enforce the fact 
that it's a valid cust_id (meaning any existing cust_id can be used 
as a template but made up template ID's - meaning an id that does 
not match an existing cust_id should be disallowed)



Thoughts?


Really goofy. They could type in any valid cust_id, theirs or not 
theirs.
What are you after with template_id.  How would your app use it. Why 
would user fill it in?


Not sure yet (new client)... for now they simply want to force the 
template column to be a valid cust_id, if it is not null... later 
I'll be digging into their design and pushing them to make some db 
architecture changes...



So randomly set it to the cust_id :).  I am not sure if a column and 
REFERENCE a column in same table.  That you'll just have to lookup or 
try.  But you probably need a check: is null or equals cust_id so they 
cannot randomly guess another cust_id. Are you sure this field 
shouldn't reference some as yet undefined template table?


I'll verify tomorrow... thx

[GENERAL] configure errors on Fedora 20

[CS_DBA]

Hi All;

when I try to compile postgresql 9.3.4 I get the below errors, anyone 
know if / which devel packages I need?


Thanks in advance




checking crypt.h usability... no
checking crypt.h presence... yes
configure: WARNING: crypt.h: present but cannot be compiled
configure: WARNING: crypt.h: check for missing prerequisite headers?
configure: WARNING: crypt.h: see the Autoconf documentation
configure: WARNING: crypt.h: section Present But Cannot Be Compiled
configure: WARNING: crypt.h: proceeding with the preprocessor's result
configure: WARNING: crypt.h: in the future, the compiler will take 
precedence

configure: WARNING: ##  ##
configure: WARNING: ## Report this to pgsql-b...@postgresql.org ##
configure: WARNING: ##  ##
checking for crypt.h... yes
checking dld.h usability... no
checking dld.h presence... no
checking for dld.h... no
checking fp_class.h usability... no
checking fp_class.h presence... no
checking for fp_class.h... no
checking getopt.h usability... no
checking getopt.h presence... yes
configure: WARNING: getopt.h: present but cannot be compiled
configure: WARNING: getopt.h: check for missing prerequisite headers?
configure: WARNING: getopt.h: see the Autoconf documentation
configure: WARNING: getopt.h: section Present But Cannot Be Compiled
configure: WARNING: getopt.h: proceeding with the preprocessor's result
configure: WARNING: getopt.h: in the future, the compiler will take 
precedence

configure: WARNING: ##  ##
configure: WARNING: ## Report this to pgsql-b...@postgresql.org ##
configure: WARNING: ##  ##
checking for getopt.h... yes
checking ieeefp.h usability... no
checking ieeefp.h presence... no
checking for ieeefp.h... no
checking ifaddrs.h usability... no
checking ifaddrs.h presence... yes
configure: WARNING: ifaddrs.h: present but cannot be compiled
configure: WARNING: ifaddrs.h: check for missing prerequisite headers?
configure: WARNING: ifaddrs.h: see the Autoconf documentation
configure: WARNING: ifaddrs.h: section Present But Cannot Be Compiled
configure: WARNING: ifaddrs.h: proceeding with the preprocessor's result
configure: WARNING: ifaddrs.h: in the future, the compiler will take 
precedence

configure: WARNING: ##  ##
configure: WARNING: ## Report this to pgsql-b...@postgresql.org ##
configure: WARNING: ##  ##
checking for ifaddrs.h... yes
checking langinfo.h usability... yes
checking langinfo.h presence... yes
checking for langinfo.h... yes
checking poll.h usability... no
checking poll.h presence... yes
configure: WARNING: poll.h: present but cannot be compiled
configure: WARNING: poll.h: check for missing prerequisite headers?
configure: WARNING: poll.h: see the Autoconf documentation
configure: WARNING: poll.h: section Present But Cannot Be Compiled
configure: WARNING: poll.h: proceeding with the preprocessor's result
configure: WARNING: poll.h: in the future, the compiler will take precedence
configure: WARNING: ##  ##
configure: WARNING: ## Report this to pgsql-b...@postgresql.org ##
configure: WARNING: ##  ##
checking for poll.h... yes
checking pwd.h usability... yes
checking pwd.h presence... yes
checking for pwd.h... yes
checking sys/ioctl.h usability... no
checking sys/ioctl.h presence... yes
configure: WARNING: sys/ioctl.h: present but cannot be compiled
configure: WARNING: sys/ioctl.h: check for missing prerequisite headers?
configure: WARNING: sys/ioctl.h: see the Autoconf documentation
configure: WARNING: sys/ioctl.h: section Present But Cannot Be 
Compiled

configure: WARNING: sys/ioctl.h: proceeding with the preprocessor's result
configure: WARNING: sys/ioctl.h: in the future, the compiler will take 
precedence

configure: WARNING: ##  ##
configure: WARNING: ## Report this to pgsql-b...@postgresql.org ##
configure: WARNING: ##  ##
checking for sys/ioctl.h... yes
checking sys/ipc.h usability... no
checking sys/ipc.h presence... yes
configure: WARNING: sys/ipc.h: present but cannot be compiled
configure: WARNING: sys/ipc.h: check for missing prerequisite headers?
configure: WARNING: sys/ipc.h: see the Autoconf documentation
configure: WARNING: sys/ipc.h: section Present But Cannot Be Compiled
configure: WARNING: sys/ipc.h: proceeding with the preprocessor's result
configure: WARNING: sys/ipc.h: in the future, the compiler will take 
precedence

configure: WARNING: ##  ##
configure: WARNING: ## Report this to pgsql-b...@postgresql.org ##
configure: WARNING: ##  ##