Re: [GENERAL] archive_command not being executed

2017-11-14 Thread Eric D 
Hi Jeremy,


Thanks for the info on 9.3 vs 9.5.  While searching for Paul's suggestion I'd 
seen the same thing, but didn't "reply all" so my response didn't make it into 
the mailing list.


Regarding your question, as I understand the process, you need to get the WAL 
files being shipped over to the standby before you start streaming replication. 
 The sequence of events I think is supposed to happen is:


  1.  Start WAL file shipping from master->standby
  2.  Run pg_basebackup from master->standby
  3.  With a recovery.conf in place, start the standby.  The recovery.conf 
needs the connection info for the master for the streaming replication, but it 
also needs the location of the WAL files, to replay what's been going on during 
the pg_basebackup.

I don't think streaming replication has a way to pick up the activity covered 
in the WAL files.



From: Jeremy Schneider 
Sent: Monday, November 13, 2017 3:56 PM
To: eric...@hotmail.com
Cc: PostgreSQL General; Paul Jungwirth
Subject: Re: [GENERAL] archive_command not being executed

Hi Eric,

Thanks for using PostgreSQL!

On Fri, Nov 10, 2017 at 9:26 AM, Paul Jungwirth
 wrote:
> Oh this has happened to me before. :-) On SB1 you need to set
> archive_mode to always (not on). Otherwise it is ignored when running as a
> standby.

It looks to me like this feature was not added until 9.5 and Eric is
running 9.3  :(

> On 11/10/2017 09:10 AM, Eric D wrote:
>> I have a standby db server (SB1) that will soon become the master.  SB1
>> is set up with streaming replication from the current master.  I'm
>> trying to set up a third server (SB2) as a slave/standby to SB1, so that
>> when SB1 becomes the master, there will be a standby for it.  First step
>> is to get WAL files shipped from SB1->SB2.

Eric,

>From my reading of the docs and commit logs, standby databases
couldn't archive their WALs until 9.5.

https://www.postgresql.org/message-id/cankgpbs7qgakgq-opzy0esam6+wue5mgpyehcgo_eoq7tjv...@mail.gmail.com
http://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-archive-mode-always/
Postgres 9.5 feature highlight - archive_mode = 
always
paquier.xyz
Postgres 9.5 feature highlight - archive_mode = always




There is a section in the 9.3 docs about cascading replication:
https://www.postgresql.org/docs/9.3/static/warm-standby.html#CASCADING-REPLICATION
PostgreSQL: Documentation: 9.3: Log-Shipping Standby 
Servers
www.postgresql.org
25.2. Log-Shipping Standby Servers. Continuous archiving can be used to create 
a high availability (HA) cluster configuration with one or more standby servers 
ready ...




It seems to me this is based on streaming replication only.  Looks
like it's not designed (in 9.3) to start archiving until it becomes a
primary.  "You will also need to set primary_conninfo in the
downstream standby to point to the cascading standby."

Are you able to configure a cascading replica by using streaming
replication on your 9.3 system, without WAL archiving on the standby?

-Jeremy

--
http://about.me/jeremy_schneider
[https://aboutme.imgix.net/background/users/j/e/r/jeremy_schneider_1364416900_46.jpg?q=80&dpr=1&auto=format&fit=max&w=250&h=140&rect=0,0,1396,732]

Jeremy Schneider on about.me
about.me
I am an Engineer, consultant, and Trainer in the United States. Read my blog.

Re: [GENERAL] jsonb

2017-11-14 Thread John R Pierce 

On 11/14/2017 2:30 PM, hmidi slim wrote:

I'm trying to use the function to_jsonb and create the name:
to_jsonb ('{"key1":" ' || 'text1' || '","key2":" ' || 'text2' || '"}');
But after that I used Objection.js ORM to get data using the query:
Product.query().where('id',1).then(prod => {console.log(prod)})
I think that the problem maybe with the usage of to_jsonb function, 
maybe I miss something. But when I fetch the data with the ORM I found 
that the type was a string and not a jsonb




never heard of your ORM... does it even know what postgres jsonb is ?   
do you know what actual SQL query that piece of ORMism generates ?




--
john r pierce, recycling bits in santa cruz



--
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] jsonb

2017-11-14 Thread Johannes Graën 
On 11/14/2017 11:30 PM, hmidi slim wrote:
> I'm trying to use the function to_jsonb and create the name:
> to_jsonb ('{"key1":" ' || 'text1' || '","key2":" ' || 'text2' || '"}');

This query converts a string into a JSON object that consist of that
string. I guess what you intend to accomplish is rather:

select jsonb_build_object('key1', 'text1', 'key2', 'text2');




-- 
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] jsonb

2017-11-14 Thread Merlin Moncure 
On Tue, Nov 14, 2017 at 4:30 PM, hmidi slim  wrote:
> I'm trying to use the function to_jsonb and create the name:
> to_jsonb ('{"key1":" ' || 'text1' || '","key2":" ' || 'text2' || '"}');
> But after that I used Objection.js ORM to get data using the query:
> Product.query().where('id',1).then(prod => {console.log(prod)})
> I think that the problem maybe with the usage of to_jsonb function, maybe I
> miss something. But when I fetch the data with the ORM I found that the type
> was a string and not a jsonb

Still not quite following. My advice would be to:

#1) work out the SQL you want the database to be running and verify
the results are correct

#2) figure out out to get the ORM to send that SQL

If you need help figuring out that SQL the ORM is actually running,
try turning on statement logging in postgresql.conf and watching the
log.

merlin


-- 
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] PGPool in Master-Master mode, is it possible?

2017-11-14 Thread Tatsuo Ishii 
> Hello There,
> 
> I need to setup two PGPool Servers in Master-Master mode, First thing I
> want to know, is it possible?
> 
> I know we can setup 2 PGPool servers in master-slave mode using watchdog.
> http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html
> 
> Could anyone please enlighten me and any workaround for this?
> 
> Regards
> VS

> I need to setup two PGPool Servers in Master-Master mode, First thing I
> want to know, is it possible?
> 
> I know we can setup 2 PGPool servers in master-slave mode using watchdog.
> http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html
> 
> Could anyone please enlighten me and any workaround for this?

This is not an appropriate list. You should go to the Pgpool mailing
list:

https://www.pgpool.net/mailman/listinfo/pgpool-general

Anyway... I am not sure what you mean by "Master-Master mode" but if
that means you want to issue write queries by connecting to any of
Pgpool-II port, it's already possible in the set up you have created.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
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] jsonb

2017-11-14 Thread hmidi slim 
I'm trying to use the function to_jsonb and create the name:
to_jsonb ('{"key1":" ' || 'text1' || '","key2":" ' || 'text2' || '"}');
But after that I used Objection.js ORM to get data using the query:
Product.query().where('id',1).then(prod => {console.log(prod)})
I think that the problem maybe with the usage of to_jsonb function, maybe I
miss something. But when I fetch the data with the ORM I found that the
type was a string and not a jsonb


2017-11-14 23:09 GMT+01:00 Merlin Moncure :

> On Tue, Nov 14, 2017 at 3:32 PM, hmidi slim  wrote:
> > I have a column name of type 'jsonb' on my table named product. The
> format
> > of the column:
> > name: {"key1": "text1", "key2": "text2"}
> >
> > When I make a query to fetch data from the table I got this format:
> > name: '{"key1": "text1", "key2": "text2"}'
> >
> > Why does postgresql returns the name such as string type and not jsonb?
> is
> > it a bug or is there something else to add?
>
> not quite following.  Can you paste the query you are trying to
> execute along with the results vs. expectation?  thanks
>
> merlin
>

Re: [GENERAL] jsonb

2017-11-14 Thread Merlin Moncure 
On Tue, Nov 14, 2017 at 3:32 PM, hmidi slim  wrote:
> I have a column name of type 'jsonb' on my table named product. The format
> of the column:
> name: {"key1": "text1", "key2": "text2"}
>
> When I make a query to fetch data from the table I got this format:
> name: '{"key1": "text1", "key2": "text2"}'
>
> Why does postgresql returns the name such as string type and not jsonb? is
> it a bug or is there something else to add?

not quite following.  Can you paste the query you are trying to
execute along with the results vs. expectation?  thanks

merlin


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

[GENERAL] jsonb

2017-11-14 Thread hmidi slim 
I have a column name of type 'jsonb' on my table named product. The format
of the column:
name: {"key1": "text1", "key2": "text2"}

When I make a query to fetch data from the table I got this format:
name: '{"key1": "text1", "key2": "text2"}'

Why does postgresql returns the name such as string type and not jsonb? is
it a bug or is there something else to add?

Re: [GENERAL] missing public on schema public

2017-11-14 Thread Stephen Frost 
Tom, all,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> =?iso-8859-1?Q?Bo_Thorbj=F8rn_Jensen?=  writes:
> > I have some additional info and a fix.
> > Firstly steps to reproduce:
> 
> Yeah, I can reproduce this.  I suspect it got broken by Stephen's hacking
> around with default ACLs.  A simple example is

Yes, it's related to the work I did with pg_dump's ACL handling, because
we're no longer just always including the whole revoke/grant set of ACLs
for everything in the output.

> $ pg_dump -c -U postgres postgres | grep -i public
> DROP SCHEMA public;
> -- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
> CREATE SCHEMA public;
> ALTER SCHEMA public OWNER TO postgres;
> -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
> COMMENT ON SCHEMA public IS 'standard public schema';
> -- Name: public; Type: ACL; Schema: -; Owner: postgres
> GRANT ALL ON SCHEMA public TO PUBLIC;
> 
> That's fine, but if I shove it through an archive file:

This works because I added into pg_dump.c a check based on if the output
is clean (and therefore the public schema is being recreated or not).

In hindsight, that wasn't really the right thing to do because it ends
up only working when pg_dump is run with -c and doesn't consider the
case where pg_dump is run without -c but pg_restore is.

> $ pg_dump -f p.dump -Fc -U postgres postgres
> 
> $ pg_restore -c p.dump | grep -i public

This doesn't work because pg_dump isn't run with -c, while pg_restore
is.  If the archive is created with pg_dump -c (as the above was), then
the results match up between the two runs.  Note also that if pg_dump is
run with -c then a pg_restore without -c would actually still include
the GRANT statement, which isn't really correct either.

That's obviously a change from what we had before and wasn't
intentional.

> This is *REALLY BAD*.  Quite aside from the restore being wrong,
> those two sequences should never ever give different results.
> Stephen, you put some filtering logic in the wrong place in pg_dump.

I do wish it was that simple.

Unfortunately, the public schema is just ridiculously special, both in
the way it's a 'user' object but is created by initdb and that it's got
special non-default ACLs on it and how it has explicit special code to
skip over it when a restore is happening, unless -c is used.

What I'm afraid we need to do here is basically continue to hack on that
code in pg_backup_archiver.c's _printTocEntry() to teach it to issue the
default GRANT ALL ON SCHEMA public TO PUBLIC; when we are processing the
TOC entry for CREATE SCHEMA public;.

That would make the recreation of the public schema when pg_dump or
pg_restore is being run with -c actually match how the public schema is
created by initdb, and the rest would end up falling into place, I
think.

One complication, however, is what happens when a user drops and
recreates the public schema.  If that's done, we'll end up not dumping
out the delta from the public schema's initial ACLs, which wouldn't be
correct if you're restoring into a newly initdb'd cluster.  I'm thinking
that we need to forcibly look at the delta from
public-as-installed-by-initdb and whatever-public-is-now, regardless of
if the public schema was recreated by the user or not, because on
restore we are expecting a newly initdb'd cluster with the public schema
as originally installed (or as installed by pg_dump/pg_restore following
the logic above).

I'll play around with this approach and see if things end up working out
in a better fashion with it.  Baking this knowledge into
pg_backup_archiver.c is certainly ugly, but handling of public has
always been hard-coded into that, and we even added more special
handling to that code 10 years ago to deal with the COMMENT on the
public schema, so this is really just more of the same.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] pg 10 crashes on int8_avg_combine

2017-11-14 Thread Dingyuan Wang 
ok, I just avoided it using sum(cnt::int) since cnt is small.

2017-11-15 00:25, Tom Lane:
> Dingyuan Wang  writes:
>> PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.2.0-8)
>> 7.2.0, 64-bit
>>
>> (gdb) bt
>> #0  int8_avg_combine (fcinfo=0x55bdb92472d8) at
>> ./build/../src/backend/utils/adt/numeric.c:4285
> 
> I think this is the same issue being discussed at
> 
> https://www.postgresql.org/message-id/flat/20171110185747.31519.28038%40wrigleys.postgresql.org
> 
>   regards, tom lane
> 
> 


-- 
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] pg_basebackup running from a remote machine

2017-11-14 Thread Laurenz Albe 
rakeshkumar464 wrote:
> If pg_basebackup is run from a remote machine with compress option --gzip ,
> compress level 9,
> will the compression occur prior to the data being sent on the network or
> after it has been received
> at the remote machine.

That only means that the output TAR file will be compressed, it has
nothing to do with the data transfered from the server.

If you want to compress the data sent over the network, use
pg_basebackup over an SSL connection with SSL compression enabled.

Yours,
Laurenz Albe



-- 
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] pg 10 crashes on int8_avg_combine

2017-11-14 Thread Tom Lane 
Dingyuan Wang  writes:
> PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.2.0-8)
> 7.2.0, 64-bit
>
> (gdb) bt
> #0  int8_avg_combine (fcinfo=0x55bdb92472d8) at
> ./build/../src/backend/utils/adt/numeric.c:4285

I think this is the same issue being discussed at

https://www.postgresql.org/message-id/flat/20171110185747.31519.28038%40wrigleys.postgresql.org

regards, tom lane


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

[GENERAL] PGPool in Master-Master mode, is it possible?

2017-11-14 Thread Vikas Sharma 
Hello There,

I need to setup two PGPool Servers in Master-Master mode, First thing I
want to know, is it possible?

I know we can setup 2 PGPool servers in master-slave mode using watchdog.
http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html

Could anyone please enlighten me and any workaround for this?

Regards
VS

[GENERAL] pg 10 crashes on int8_avg_combine

2017-11-14 Thread Dingyuan Wang 
Hi,

PostgreSQL constantly crashes on this query:

select dategrid, category, sum(cnt) from v_crime_grid group by dategrid,
category;

Where v_crime_grid is a materialized view, have columns "dategrid" int4,
"category" int4, and "cnt" int8.

Version is:

PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.2.0-8)
7.2.0, 64-bit

GDB bt says:

(gdb) bt
#0  int8_avg_combine (fcinfo=0x55bdb92472d8) at
./build/../src/backend/utils/adt/numeric.c:4285
#1  0x55bdb73b2e4d in advance_combine_function
(pergroupstate=0x55bdb92481b8, pertrans=0x55bdb92471a8,
aggstate=0x55bdb9239378) at ./build/../src/backend/executor/nodeAgg.c:1233
#2  combine_aggregates (aggstate=0x55bdb9239378, pergroup=) at ./build/../src/backend/executor/nodeAgg.c:1167
#3  0x55bdb73b36bd in agg_retrieve_direct (aggstate=0x55bdb9239378)
at ./build/../src/backend/executor/nodeAgg.c:2407
#4  ExecAgg (pstate=0x55bdb9239378) at
./build/../src/backend/executor/nodeAgg.c:2124
#5  0x55bdb73a555d in ExecProcNode (node=0x55bdb9239378) at
./build/../src/include/executor/executor.h:250
#6  ExecutePlan (execute_once=, dest=0x55bdb9238dd8,
direction=, numberTuples=0, sendTuples=,
operation=CMD_SELECT, use_parallel_mode=,
planstate=0x55bdb9239378, estate=0x55bdb9239138) at
./build/../src/backend/executor/execMain.c:1721
#7  standard_ExecutorRun (queryDesc=0x55bdb9202f48, direction=, count=0, execute_once=) at
./build/../src/backend/executor/execMain.c:363
#8  0x7f5e48c1a0c5 in pgss_ExecutorRun (queryDesc=0x55bdb9202f48,
direction=ForwardScanDirection, count=0, execute_once=)
at ./build/../contrib/pg_stat_statements/pg_stat_statements.c:889
#9  0x55bdb74dc456 in PortalRunSelect
(portal=portal@entry=0x55bdb918a7a8, forward=forward@entry=1 '\001',
count=0, count@entry=9223372036854775807, dest=dest@entry=0x55bdb9238dd8)
at ./build/../src/backend/tcop/pquery.c:932
#10 0x55bdb74dda40 in PortalRun (portal=portal@entry=0x55bdb918a7a8,
count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=1
'\001', run_once=run_once@entry=1 '\001',
dest=dest@entry=0x55bdb9238dd8,
altdest=altdest@entry=0x55bdb9238dd8, completionTag=0x7ffd7dd99cd0 "")
at ./build/../src/backend/tcop/pquery.c:773
#11 0x55bdb74d9580 in exec_simple_query (query_string=0x55bdb9153348
"select dategrid, category, sum(cnt) from v_crime_grid group by
dategrid, category;")
at ./build/../src/backend/tcop/postgres.c:1099
#12 0x55bdb74db551 in PostgresMain (argc=,
argv=argv@entry=0x55bdb9188c50, dbname=,
username=) at ./build/../src/backend/tcop/postgres.c:4090
#13 0x55bdb721cd23 in BackendRun (port=0x55bdb91818b0) at
./build/../src/backend/postmaster/postmaster.c:4357
#14 BackendStartup (port=0x55bdb91818b0) at
./build/../src/backend/postmaster/postmaster.c:4029
#15 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1753
#16 0x55bdb746b7bf in PostmasterMain (argc=5, argv=0x55bdb9133e40)
at ./build/../src/backend/postmaster/postmaster.c:1361
#17 0x55bdb721e834 in main (argc=5, argv=0x55bdb9133e40) at
./build/../src/backend/main/main.c:228


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

[GENERAL] pg_basebackup running from a remote machine

2017-11-14 Thread rakeshkumar464 
If pg_basebackup is run from a remote machine with compress option --gzip ,
compress level 9,
will the compression occur prior to the data being sent on the network or
after it has been received
at the remote machine.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
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] missing public on schema public

2017-11-14 Thread Tom Lane 
=?iso-8859-1?Q?Bo_Thorbj=F8rn_Jensen?=  writes:
> I have some additional info and a fix.
> Firstly steps to reproduce:

Yeah, I can reproduce this.  I suspect it got broken by Stephen's hacking
around with default ACLs.  A simple example is

$ pg_dump -c -U postgres postgres | grep -i public
DROP SCHEMA public;
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO postgres;
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
COMMENT ON SCHEMA public IS 'standard public schema';
-- Name: public; Type: ACL; Schema: -; Owner: postgres
GRANT ALL ON SCHEMA public TO PUBLIC;

That's fine, but if I shove it through an archive file:

$ pg_dump -f p.dump -Fc -U postgres postgres

$ pg_restore -c p.dump | grep -i public
DROP SCHEMA public;
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO postgres;
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
COMMENT ON SCHEMA public IS 'standard public schema';

This is *REALLY BAD*.  Quite aside from the restore being wrong,
those two sequences should never ever give different results.
Stephen, you put some filtering logic in the wrong place in pg_dump.

regards, tom lane


-- 
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] Retrieve the server's time zone

2017-11-14 Thread Thomas Kellerer 
Tom Lane schrieb am 14.11.2017 um 15:36:
>> is there any way (short of writing a function in an untrusted PL)
>> to determine the actual time zone (or time) of the server OS?
> 
> AFAIK that would only be true if some part of your client stack
> is issuing a SET TIMEZONE command.  (libpq will do that if it finds
> a PGTZ environment variable set, but not in response to plain TZ.)

Ah, interesting. I do that through JDBC, so apparently that's the part to 
blame. 
 
> If that's true, and you can't/don't want to change it, you could try
> 
> select reset_val from pg_settings where name = 'TimeZone';

Hmm, this does not seem to work. 

I am connected to a server with Asia/Bangkok but through JDBC 
that query still returns Europe/Berlin (which is my client's time zone)

So apparently the JDBC driver somehow "persists" this setting. 

I will take this to the JDBC mailing list then, thanks.

Thomas



-- 
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] Retrieve the server's time zone

2017-11-14 Thread Tom Lane 
Thomas Kellerer  writes:
> is there any way (short of writing a function in an untrusted PL) to 
> determine the actual time zone (or time) of the server OS? 

The default value of the timezone parameter is as close as you'll get
in modern versions of PG.

> "show timezone" always returns the client's time zone. 

AFAIK that would only be true if some part of your client stack
is issuing a SET TIMEZONE command.  (libpq will do that if it finds
a PGTZ environment variable set, but not in response to plain TZ.)

If that's true, and you can't/don't want to change it, you could try

select reset_val from pg_settings where name = 'TimeZone';

regards, tom lane


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

[GENERAL] Re: PostgreSQL walsender process doesn't exist after "pg_ctl stop -m fast"

2017-11-14 Thread y39chen 
Thank you for the explanation. We shall try the latest PostgreSQL 9.6.6
version.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
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] Because PostgreSQL is compiling in old versions of OS?

2017-11-14 Thread Jose Maria Terry Jimenez 

El 11/11/17 a las 0:48, DrakoRod escribió:

Oh!!


Jose Maria TJ wrote

You're wrong, that are gcc versions, not OS versions.

For example in my CentOS 6 Box

cat /etc/redhat-release
CentOS release 6.9 (Final)

gcc -v
[...trimmed...]
gcc versión 4.4.7 20120313 (Red Hat 4.4.7-18) (GCC)

You're right!. Is the GGC version, not the OS version

Great! I think that I compiling in a GGC 4.X version is good for most SO
distribution right?

Thanks!



You're welcome!


--
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] missing public on schema public

2017-11-14 Thread Bo Thorbjørn Jensen 
I have some additional info and a fix.

Firstly steps to reproduce:

1.  create database:
CREATE DATABASE test WITH ENCODING='UTF8' OWNER=postgres CONNECTION LIMIT=-1;
-- here public has access to public

2. dump:
pg_dump -f testfile.dump -F c -h localhost -U postgres test

3. restore:
pg_restore -c -d testfile.dump -h localhost -U postgres test
-- here public no longer has access to schema public

It is easily fixable with:
GRANT ALL ON SCHEMA public TO public;

And the issue goes away.. (privilege stays after next dump/restore)

So. What am I missing?
Is this intentional functionality ?

Kind regards and again thank you for your time

Bo Thorbjørn Jensen

[GENERAL] Connection loosing at some places - caused by firewall

2017-11-14 Thread Durumdara 
Dear Members!


Windows 10, PGSQL 9.4 and 9.6 (Debian with SSL, and Windows without it - it
doesn't matter).

When I (or my boss) work(s) at home, I got connection lost errors from
PGAdmin (3/4) or from other applications too.


server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.


PGAdmin, EMS PostgreSQL manager, Navicat for PGSQL, and psql too.

When minimum 5 minutes passed after the last request (select, insert, or
any communication what I did) the connection lost.

The keepalive timer is 10 minutes in the server - so it is not the source
of the problem.

I tried to set keepalive in the clients (EMS, Navicat called ping) to lower
- no effect.

When I did any request in my side before the 5 minutes "limit" passed, I
got a new 5 minutes.

It seems to be libpq.dll applications need traffic (started by the client)
in 5 minute periods to keep the connection.

In the office I don't have this limitation.

My colleauge can work at home without time limit, and he has same Internet
Provider as mine.

It's not PGSQL server based.
When I use VPN connection from home to reach office PG server, I also got
this problem.
>From the office I can use same IP, same server without problem.

I checked the router, I disabled all things - nothing happened.

*I disabled my firewall at home - the problem vanished!!!*

And: if I use a secondary test computer (also Windows 10, PGAdmin III) - it
works at home!!!

I very-very wondered because it seems to be computer firewall problem.
I tried to use wifi and direct cable - no differents, so network card could
be eliminated from the "problem sources" list.

It's very interesting!
The firewall could make differents between networks and limits these
packages timeout to 5 minutes? Hmmm...

I made an exception to the firewall - nothing happened.

I know this is not Windows Admin list, but you may experienced same
problem,  or you have idea about what to do.

This is become important for us because one of my customer have same
problem, and we don't know the solution...

What is your opinion about it?

Thanks
  dd

[GENERAL] Retrieve the server's time zone

2017-11-14 Thread Thomas Kellerer 
Hello,

is there any way (short of writing a function in an untrusted PL) to determine 
the actual time zone (or time) of the server OS? 

"show timezone" always returns the client's time zone. 

localtimestamp also converts the server's time to the client time zone (the one 
defined by "timezone")

log_timezone is also unreliable as it can be changed to anything.

I am looking for something along the lines of: "show server_timezone" or 
"select current_timestamp at time zone server_timezone" 

Is that possible? 




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