Re: [GENERAL] Migrating plattaform

[John R Pierce]

On 11/8/2017 11:38 AM, Valdir Kageyama wrote:


I need migrated the postgres from Linux on IBM Power to Oracle Linux 
on SPARC.


My doubt is possible copy the datafiles to new enviorement ? or I need 
using  other means of copying the data.

For exemples: pg_dump/pg_restore.



pretty sure you can't copy binary database files between architectures, 
as various data structures have different binary representations.


sure, pg_dump  -Fc | pg_restore, that works fine across architectures.


--
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] Postgres 10.1 fails to start: server did not start in time

[Tom Lane]

Andres Freund  writes:
> we could really do better than just wonder whether our signal to
> shutdown was received or not.  There probably should be a quite short
> timeout for the server to change status, and then a much longer one for
> that shutdown to finish.

While I don't want to just raise the timeout, I could get behind a more
thorough rethinking of the behavior there.

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] Multiple unnests in query

[Tom Lane]

Aron Widforss  writes:
> Is this first query expected behavior? If so, what is the rationale?

The short answer is "because it's always worked that way".  You
might find the last half of section 37.4.8 illuminating:

https://www.postgresql.org/docs/devel/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

but if you're on a pre-v10 release, pay close attention to what it says
about the difference between v10 and pre-v10 behavior.

> I would have expected nine rows returned (as in my second example).

Your second example has approximately nothing to do with your first.
It has only one SRF in the SELECT list, so there's not much doubt
about what ought to happen.

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] Multiple unnests in query

[Aron Widforss]

Good night,

Is this first query expected behavior? If so, what is the rationale? I
would have expected nine rows returned (as in my second example).

Regards,
Aron Widforss

SELECT
  unnest(ARRAY[1, 1, 2]) AS unnested1,
  unnest(ARRAY[3, 3, 4]) AS unnested2
;
 unnested1 | unnested2 
---+---
 1 | 3
 1 | 3
 2 | 4
(3 rows)

SELECT
  unnest(ARRAY[1, 1, 2]) AS unnested1,
  sec
FROM (SELECT 3 AS sec UNION ALL
  SELECT 3 AS sec UNION ALL
  SELECT 4 AS sec) test
;
 unnested1 | sec 
---+-
 1 |   3
 1 |   3
 2 |   3
 1 |   3
 1 |   3
 2 |   3
 1 |   4
 1 |   4
 2 |   4
(9 rows)


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

[GENERAL] ways of monitoring logical decoding performance

[Weiping Qu]

Dear all,

I'd like to monitor the resource utilization of logical decoding (e.g. 
in version 9.5).
For example, I'd like to see the wal buffer hit ratio, i.e. how much 
reading for logical decoding is from in-memory pages.

This can be set by blks_hit/(blks_read+blks_hit) from pg_stat_database.
But this values might include numbers incurred by other concurrent sessions.

Is there any clear manner to entirely focus on the performance of 
logical decoding?


Regards,
Weiping



--
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] Postgres 10.1 fails to start: server did not start in time

[Andres Freund]

On 2017-11-12 14:26:42 -0500, Tom Lane wrote:
> Christoph Berg  writes:
> > The default systemd timeout seems to be 90s. I have already changed
> > the systemd timeout to infinity (start) and 1h (stop), so only the
> > default pg_ctl timeout remains (60s), which I'd rather not override
> > unilaterally.
> 
> > That said, isn't 60s way too small for shutting down larger clusters?
> > And likewise for starting?
> 
> Well, that's tied into the fact that pg_ctl doesn't disturb the server's
> state if it gives up waiting.  If it did, we would certainly use a larger
> timeout or none at all.

Hm. So partially that's also related to the fact that we didn't have a
good way to know whether the server reacted to the shutdown request or
not. With the infrastructure from

commit f13ea95f9e473a43ee4e1baeb94daaf83535d37c
Author: Tom Lane 
Date:   2017-06-28 17:31:24 -0400

Change pg_ctl to detect server-ready by watching status in postmaster.pid.

we could really do better than just wonder whether our signal to
shutdown was received or not.  There probably should be a quite short
timeout for the server to change status, and then a much longer one for
that shutdown to finish.


> I don't feel a big need to change that default,
> but if you have a surrounding script that is going to take adverse action
> after a timeout then you need to use a larger value ...

Didn't we have to fiddle with this a bunch in the regression tests, to
get things to work properly on slow animals? If we had to do that, other
people had to do so as well. Not the friendliest experience...

Greetings,

Andres Freund


-- 
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 on Debian servers

[rob stone]

On Sat, 2017-11-11 at 14:30 +0100, Magnus Hagander wrote:
> 
> 
> 
> The init.d script is not used with systemd.
> 
>  
> 
Hello Magnus,

Many months ago on a bog standard Debian set-up did a re-boot and ended
up with postmasters running for 9.2, 9.4, 9.5 and 9.6 all started one
after the other. There was a script in init.d which read thru
/usr/lib/postgresql and it started running Postgres for each version it
found. Fortunately, all listening on different ports.

The fix was to disable that script as well as the systemd service.

Doing the upgrade to 10 in a few weeks. Will let you know how it goes.

I assume you are aware of this DSA:-


Debian Security Advisory DSA-4029-1

---

Package: postgresql-common
CVE ID : CVE-2017-8806

It was discovered that the pg_ctlcluster, pg_createcluster and
pg_upgradecluster commands handled symbolic links insecurely which
could result in local denial of service by overwriting arbitrary files.

For the oldstable distribution (jessie), this problem has been fixed
in version 165+deb8u3.

For the stable distribution (stretch), this problem has been fixed in
version 181+deb9u1.


Cheers,
Rob




-- 
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] Postgres 10.1 fails to start: server did not start in time

[Tom Lane]

Christoph Berg  writes:
> The default systemd timeout seems to be 90s. I have already changed
> the systemd timeout to infinity (start) and 1h (stop), so only the
> default pg_ctl timeout remains (60s), which I'd rather not override
> unilaterally.

> That said, isn't 60s way too small for shutting down larger clusters?
> And likewise for starting?

Well, that's tied into the fact that pg_ctl doesn't disturb the server's
state if it gives up waiting.  If it did, we would certainly use a larger
timeout or none at all.  I don't feel a big need to change that default,
but if you have a surrounding script that is going to take adverse action
after a timeout then you need to use a larger value ...

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] Postgres 10.1 fails to start: server did not start in time

[Christoph Berg]

Re: Tom Lane 2017-11-12 <20802.1510513...@sss.pgh.pa.us>
> Agreed, but I think Peter has a point: why is there a timeout at all,
> let alone one as short as 30 seconds?  Since systemd doesn't serialize
> service starts unnecessarily, there seems little value in giving up
> quickly.  And we know that cases such as crash recovery may take more
> than that.

The default systemd timeout seems to be 90s. I have already changed
the systemd timeout to infinity (start) and 1h (stop), so only the
default pg_ctl timeout remains (60s), which I'd rather not override
unilaterally.

https://anonscm.debian.org/cgit/pkg-postgresql/postgresql-common.git/tree/systemd/postgresql@.service#n18

That said, isn't 60s way too small for shutting down larger clusters?
And likewise for starting?

Christoph


-- 
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 on Debian servers

[Karsten Hilbert]

On Sat, Nov 11, 2017 at 01:03:18PM +, Mark Morgan Lloyd wrote:

> Several legacy programs written in Delphi ground to a halt this morning,
> which turned out to be because a Debian system had updated its copy of
> PostgreSQL and restarted the server, which broke any live connections.
> 
> At least some versions of Delphi, not to mention other IDE/RAD tools with
> database-aware components, don't automatically try to reestablish a database
> session that's been interrupted. In any event, an unexpected server restart
> (irrespective of all investment in UPSes etc.) has the potential of playing
> havoc on a clustered system.
> 
> Is there any way that either the package maintainer or a site
> administrator/programmer such as myself can mark the Postgres server
> packages as "manual upgrade only" or similar? Or since I'm almost certainly
> not the first person to be bitten by this, is there a preferred hack in
> mitigation?

Apart from that (putting packages on hold), PostgreSQL
updates on Debian don't upgrade existing clusters
automatically. They do create a new cluster but the old one
is kept around and stays running, IIRC even on the very same
port.

(Having gone all the way from PG 7.1 to PG 10 on Debian :)

What did

pg_lsclusters

say ?

There must have been something additional at play.

Regards,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Postgres 10.1 fails to start: server did not start in time

[Tom Lane]

Christoph Berg  writes:
> Re: Peter J. Holzer 2017-11-12 <20171112173559.m6chmbyf4vz6f...@hjp.at>
>> Wouldn't it be better to remove the timeout?

> If you don't want to block, don't depend on the database service. That
> question is independent from the timeout.

Agreed, but I think Peter has a point: why is there a timeout at all,
let alone one as short as 30 seconds?  Since systemd doesn't serialize
service starts unnecessarily, there seems little value in giving up
quickly.  And we know that cases such as crash recovery may take more
than that.

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] Invalid client charset when using TDS_FDW

[Igal @ Lucee.org]

Hi,

I am trying to connect to a MSSQL database via the tds_fdw extension.  
I've installed the extension version 1.0.8 from BIGSQL on a Windows machine.


I get a client charset invalid though, and I'm not sure where it is set 
or what to set it to:


config.c:886:Setting tds version to 7.3 (0x703).
config.c:788:TDS version set to 7.3 from $TDSVER.
config.c:765:Setting 'dump_file' to 'C:\temp\freetds.log' from $TDSDUMP.
dblib.c:1237:tdsdbopen: Calling tds_connect_and_login(003DF9D0, 
003BE2B0)
iconv.c:328:tds_iconv_open(003DF9D0, 
CP1252;LC_CTYPE=English_United 
States.1252;LC_MONETARY=C;LC_NUMERIC=C;LC_TIME=C)

iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
iconv.c:187:local name for UTF-8 is UTF-8
iconv.c:187:local name for UCS-2LE is UCS-2LE
iconv.c:187:local name for UCS-2BE is UCS-2BE
iconv.c:346:setting up conversions for client charset 
"CP1252;LC_CTYPE=English_United 
States.1252;LC_MONETARY=C;LC_NUMERIC=C;LC_TIME=C"
iconv.c:348:preparing iconv for "CP1252;LC_CTYPE=English_United 
States.1252;LC_MONETARY=C;LC_NUMERIC=C;LC_TIME=C" <-> "UCS-2LE" conversion

iconv.c:423:tds_iconv_info_init: *client charset name "-1" invalid*

It looks like the error comes form 
https://github.com/wankdanker/freetds/blob/master/src/tds/iconv.c#L424


I believe that I can set the "client charset" in a .freetds.conf file, 
but I'm not sure what to set it to since CP1252 seems very valid.  Or 
perhaps it should be set to "CP1252" only without the Locale information 
afterwards?


Any ideas?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: [GENERAL] Combine multiple text search configuration

[Johannes Graën]

Hi,


On 2017-11-06 09:17, hmidi slim wrote:
> Hi,
> I want to know if I can combine multiple text search configurations when
> I tried to use FTS.
> Is there any options like this:
> *to_tsvector(['english', 'french'], document)*
> *
> *
> Trying to create a new text configuration:
> *Create text search configuration test (copy=simple)*
> *Alter text search configuration test*
> *add mapping for asciiword with english_stem,french_stem*
> *
> *
> This query doesn't work. How can I combine multiple text search
> configurations if I need more than one into my query to search a word?

what about using two indexes, one for each language? If your documents
can either be English OR French, the English OR the French vector should
match an English OR French tsquery.

It is not clear to me how combining two stemmers should practically work
since each word can only have one stem. If you have multilingual
documents or texts with code switching, you could also try combining the
two vectors both for the documents and the query:

(to_tsvector('english', document) || to_tsvector('french', document)) @@
(to_tsquery('english', query) || to_tsquery('french', query))



-- 
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] Combine multiple text search configuration

[Johannes Graën]

On 2017-11-07 08:27, hmidi slim wrote:
> Hi, 
> Thank for your proposition but when to use this query : 
> (to_tsvector('english', document) || to_tsvector('french', document)) @@
> (to_tsquery('english', query) || to_tsquery('french', query))
> I think that the performance decrease and not a good solution for big
> amount of data. Is it?

You have more lexems when you combine two languages, but not twice as
many as there will be some overlap. That means your index will also be
be bigger than a single language index. Anyhow I would expect this
variant to perform better than querying two single columns
simultaneously. Maybe one of the FTS developers could comment on this?


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

[GENERAL] sync the data's from catalog table

[Dinesh kumar]

Hi Team,

How can I sync the data's from pg_authid to manually created table (user
table) whenever the update or insert happens on pg_authid table.

Thanks

Dinesh Kumar

[GENERAL] Migrating plattaform

[Valdir Kageyama]

Hello,

I need migrated the postgres from Linux on IBM Power to Oracle Linux on
SPARC.

My doubt is possible copy the datafiles to new enviorement ? or I need
using  other means of copying the data.
For exemples: pg_dump/pg_restore.


regards

valdir

Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

[Christoph Berg]

Re: Peter J. Holzer 2017-11-12 <20171112173559.m6chmbyf4vz6f...@hjp.at>
> Wouldn't it be better to remove the timeout? If some other service
> depends on PostgreSQL it probably shouldn't be startet until PostgreSQL
> is really up and services which don't need PostgreSQL (e.g. SSH or X11
> login or a web- or mail server) shouldn't depend on it.
> 
> One of the purported advantages of systemd over SystemV init is that it
> starts up services in parallel, so a service which takes a long (or
> infinite) time to start doesn't block other services.

If you don't want to block, don't depend on the database service. That
question is independent from the timeout.

Christoph


-- 
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] Postgres 10.1 fails to start: server did not start in time

[Peter J. Holzer]

On 2017-11-12 13:26:58 +0100, Christoph Berg wrote:
> Re: To Adam Brusselback 2017-11-11 
> <2017205316.u56lkmkakdmcx...@msg.df7cb.de>
> > I'm investigating if it's a good idea to tell systemd to ignore the
> > exit code of pg_ctl(cluster).
> 
> Telling systemd to ignore ExecStart errors seems to be the correct
> solution. The service will still be active, with the startup error
> being shown:

Wouldn't it be better to remove the timeout? If some other service
depends on PostgreSQL it probably shouldn't be startet until PostgreSQL
is really up and services which don't need PostgreSQL (e.g. SSH or X11
login or a web- or mail server) shouldn't depend on it.

One of the purported advantages of systemd over SystemV init is that it
starts up services in parallel, so a service which takes a long (or
infinite) time to start doesn't block other services.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

[Christoph Berg]

Re: To Adam Brusselback 2017-11-11 
<2017205316.u56lkmkakdmcx...@msg.df7cb.de>
> I'm investigating if it's a good idea to tell systemd to ignore the
> exit code of pg_ctl(cluster).

Telling systemd to ignore ExecStart errors seems to be the correct
solution. The service will still be active, with the startup error
being shown:

● postgresql@10-main.service - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled; vendor 
preset: enabled)
   Active: active (running) since Sun 2017-11-12 13:24:21 CET; 210ms ago
  Process: 31892 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m 
fast 10-main stop (code=exited, status=0/SUCCESS)
  Process: 31922 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 
10-main start (code=exited, status=1/FAILURE)
 Main PID: 31928 (postgres)
Tasks: 8 (limit: 4915)
   CGroup: /system.slice/system-postgresql.slice/postgresql@10-main.service
   ├─31928 /usr/lib/postgresql/10/bin/postgres -D 
/var/lib/postgresql/10/main -c 
config_file=/etc/postgresql/10/main/postgresql.conf
   ├─31931 postgres: 10/main: checkpointer process
   ├─31932 postgres: 10/main: writer process
   ├─31933 postgres: 10/main: wal writer process
   ├─31934 postgres: 10/main: autovacuum launcher process
   ├─31935 postgres: 10/main: archiver process
   ├─31936 postgres: 10/main: stats collector process
   └─31937 postgres: 10/main: bgworker: logical replication launcher

Nov 12 13:24:20 lehmann systemd[1]: Starting PostgreSQL Cluster 10-main...
Nov 12 13:24:21 lehmann postgresql@10-main[31922]: Error: 
/usr/lib/postgresql/10/bin/pg_ctl /usr/lib/postgresql/10/bin/pg_ctl start -D 
/var/lib/postgresql/10/main -l /var/log/postgresql/postgresql-10-main.log -t 0 
-s -o  -c config_file="/etc/postgresql/10/main/postgresql.conf"  exited with 
status 1:
Nov 12 13:24:21 lehmann postgresql@10-main[31922]: pg_ctl: server did not start 
in time
Nov 12 13:24:21 lehmann systemd[1]: postgresql@10-main.service: PID file 
/var/run/postgresql/10-main.pid not readable (yet?) after start: No such file 
or directory
Nov 12 13:24:21 lehmann systemd[1]: Started PostgreSQL Cluster 10-main.

Fixed in 
https://anonscm.debian.org/cgit/pkg-postgresql/postgresql-common.git/commit/?id=fc57e655c71e8f6bcb3010b054f5adbf32a224d7
 , thanks for the report!

Christoph


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