RE: How to watch for schema changes

[Igor Neyman]

-Original Message-
From: Igor Korot [mailto:ikoro...@gmail.com] 
Sent: Monday, December 03, 2018 8:29 AM
To: Adrian Klaver 
Cc: pgsql-general 
Subject: Re: How to watch for schema changes

...

And executing LISTEN will also work for ODBC connection, right?

Thank you.

___

It's been years since we dealt with this problem, so the details are fuzzy.

All applications in the package we develop connect to PG using ODBC, but one 
app that's using LISTEN is connecting to PG through native interface libpq.dll, 
ODBC didn't work for that purpose, at least at the time.

Regards,
Igor Neyman

Re: How to watch for schema changes

[Igor Korot]

Igor,

On Tue, Dec 4, 2018 at 8:20 AM Igor Neyman  wrote:
>
>
> -Original Message-
> From: Igor Korot [mailto:ikoro...@gmail.com]
> Sent: Monday, December 03, 2018 8:29 AM
> To: Adrian Klaver 
> Cc: pgsql-general 
> Subject: Re: How to watch for schema changes
>
> ...
>
> And executing LISTEN will also work for ODBC connection, right?
>
> Thank you.
>
> ___
>
> It's been years since we dealt with this problem, so the details are fuzzy.
>
> All applications in the package we develop connect to PG using ODBC, but one 
> app that's using LISTEN is connecting to PG through native interface 
> libpq.dll, ODBC didn't work for that purpose, at least at the time.

I will try it and report back.
Out of curiosity - when was the last time you tried?

Thank you.

>
> Regards,
> Igor Neyman

RE: How to watch for schema changes

[Igor Neyman]

-Original Message-
From: Igor Korot [mailto:ikoro...@gmail.com] 
Sent: Tuesday, December 04, 2018 11:07 AM
To: Igor Neyman 
Cc: Adrian Klaver ; pgsql-general 

Subject: Re: How to watch for schema changes

Igor,

On Tue, Dec 4, 2018 at 8:20 AM Igor Neyman  wrote:
>
>
> -Original Message-
> From: Igor Korot [mailto:ikoro...@gmail.com]
> Sent: Monday, December 03, 2018 8:29 AM
> To: Adrian Klaver 
> Cc: pgsql-general 
> Subject: Re: How to watch for schema changes
>
> ...
>
> And executing LISTEN will also work for ODBC connection, right?
>
> Thank you.
>
> ___
>
> It's been years since we dealt with this problem, so the details are fuzzy.
>
> All applications in the package we develop connect to PG using ODBC, but one 
> app that's using LISTEN is connecting to PG through native interface 
> libpq.dll, ODBC didn't work for that purpose, at least at the time.

I will try it and report back.
Out of curiosity - when was the last time you tried?

Thank you.
_

PG release 8.4.

Re: postgis after pg_upgrade

[Jeremy Schneider]

On 11/30/18 05:11, Slavcho Trnkovski wrote:
> I have postgresql 9.4 with postgis extension installed (latest version,
> 2.4.5). I upgraded postgresql from 9.4 to 9.6. 
> After upgrading to 9.6 I get the following result
> from PostGIS_full_version()
>  select PostGIS_full_version();
> 
>  ... (procs need upgrade for use with "96") ...
> 
> Is there any way to resolve this besides recreating the extension?

If I'm reading the postgis docs correctly, this message specifically
means that you must do a full dump/reload of postgis-enabled databases.

http://postgis.net/docs/manual-2.4/postgis_installation.html#upgrading

> If you can't find the postgis_upgrade*.sql specific for upgrading
> your version you are using a version too early for a soft upgrade
> and need to do a HARD UPGRADE.
>
> The PostGIS_Full_Version function should inform you about the need
> to run this kind of upgrade using a "procs need upgrade" message.
>
> By HARD UPGRADE we mean full dump/reload of postgis-enabled
> databases.

-- 
http://about.me/jeremy_schneider

simple division

[Martin Mueller]

I have asked this question before and apologize for not remembering it.  How do 
you do simple division in postgres and get 10/4 with decimals?

This involves cast and numeric in odd ways that are not well explained in the 
documentation. For instance, you’d expect an example in the Mathematical 
Functions. But there isn’t.

The documentation of string functions is exemplary. The documentation of 
mathematical less so. Remember that it may be used by folks like me whose math 
is shaky. The MySQL documentation is better on this simple operation.


-
Martin Mueller
Professor emeritus of English and Classics
Northwestern University

Geographic coordinate values format conversion to DD (Decimal Degrees) format

[Allan Kamau]

I have data that contains geographic coordinate values in various
geographic coordinate system formats such as "degrees minutes seconds" and
"degrees decimal minutes".
I would like to convert these geographic coordinate values into decimal
degrees format.
Does PostgreSQL (more specifically PostGIS) have functions for these types
of conversions.

Below are examples of the geographic coordinates values I have coupled with
the resulting decimal degrees values.
39.529053 N 107.772406 W=39.5290530°, -107.7724060°
27.485973 S 153.190699 E=-27.4859730°, 153.1906990°
30°32’39” N, 91°07’36” E=30.5441667°, 091.127°
27.485973 S 153.190699 E=-27.4859730°, 153.1906990°
1¡20'1N 103¡45'15E=01.3336111°, 103.7541667°
S 20 10.8035165 W 176 36.074496=-20.1800586°, -176.6012416°

The "°" sign in the results is optional.

Allan.

Re: simple division

[Joshua D. Drake]

On 12/4/18 12:29 PM, Martin Mueller wrote:


I have asked this question before and apologize for not remembering 
it. How do you do simple division in postgres and get 10/4 with decimals?


This involves cast and numeric in odd ways that are not well explained 
in the documentation. For instance, you’d expect an example in the 
Mathematical Functions. But there isn’t.


The documentation of string functions is exemplary. The documentation 
of mathematical less so. Remember that it may be used by folks like me 
whose math is shaky. The MySQL documentation is better on this simple 
operation.



I may be misunderstanding the question but:


select cast(x/y as numeric(10,4));

JD




-

Martin Mueller
Professor emeritus of English and Classics

Northwestern University



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

RE: simple division

[Igor Neyman]

From: Martin Mueller 
Sent: Tuesday, December 4, 2018 3:30 PM
To: pgsql-general 
Subject: simple division

I have asked this question before and apologize for not remembering it.  How do 
you do simple division in postgres and get 10/4 with decimals?

This involves cast and numeric in odd ways that are not well explained in the 
documentation. For instance, you’d expect an example in the Mathematical 
Functions. But there isn’t.

The documentation of string functions is exemplary. The documentation of 
mathematical less so. Remember that it may be used by folks like me whose math 
is shaky. The MySQL documentation is better on this simple operation.


-
Martin Mueller
Professor emeritus of English and Classics
Northwestern University

There is nothing odd about:

select (12345678.1234/32.5678)::numeric(10,4);

Regards,
Igor Neyman

Re: simple division

[Thomas Kellerer]

Martin Mueller schrieb am 04.12.2018 um 21:29:

I have asked this question before and apologize for not remembering
it.  How do you do simple division in postgres and get 10/4 with
decimals?


In the expression 10/4 both numbers are integers.
And an integer divsion does not yield decimals (that's the same as in every 
strongly typed programming language).

I am not entirely sure what the SQL standard says about such an expression, but 
e.g. SQL Server, SQLite, Firebird and DB2 behave the same as Postgres.
That is they apply integer division if all values are integers, and decimal 
division if at least one value is a decimal.

To get a division of decimals you need to specify at least one value as a 
decimal,

e.g. "select 10.0/4" or "select 10/4.0" whatever you prefer.

Re: simple division

[David G. Johnston]

On Tue, Dec 4, 2018 at 1:29 PM Martin Mueller
 wrote:
> I have asked this question before and apologize for not remembering it.  How 
> do you do simple division in postgres and get 10/4 with decimals?
> This involves cast and numeric in odd ways that are not well explained in the 
> documentation. For instance, you’d expect an example in the Mathematical 
> Functions. But there isn’t.

select 10/4, 10.0/4, 10/4.0, 10.0/4.0;

The first one returns 2, the rest of them 2.5 - from which one can
infer that if both inputs are integer (type) the output is integer
(type) - if at least one input is non-integer (type) the output will
be as well.

If you want to cast...select 10/(4::numeric)...

David J.

Re: simple division

[David G. Johnston]

On Tue, Dec 4, 2018 at 1:38 PM Joshua D. Drake  wrote:
> I may be misunderstanding the question but:
Indeed...
> select cast(x/y as numeric(10,4));

Your answer is 2.0 instead of the correct 2.5 - you need to cast
before the division, not after.

David J.

querying both text and non-text properties

[Rob Nikander]

Hi, 

I’ve got an application where I’d like to search a collection of objects based 
on various properties, some text and others non-text (bools, enums, ints, etc). 
I’ve used full text search before, following the PG docs to set up a index on a 
ts_vector. And of course I’ve used normal indexes before for accelerating basic 
queries that use non-text columns.

Any recommendations on combining the two? For example, if I search for objects 
where the description text matches [some ts_query] and the color = red, I can 
imagine putting the color property into the text index somehow - maybe with 
tokens like: ’color_red’, ‘color_blue’, but for something like an integer … ?

Rob

Re: simple division

[Martin Mueller]

I didn't formulate my question properly, because the query went like
 "select alldefects /wordcount" 
where alldefects and wordcount are integers.   But none of the different ways 
of putting the double colon seemed to work. 

The Postgres notation of this simple procedure is very unintuitive. I haven't 
been able to remember several times, and most people think of me as a person 
with a reasonably good memory. There is no obvious place in the documentation 
to look this up.


On 12/4/18, 2:45 PM, "David G. Johnston"  wrote:

On Tue, Dec 4, 2018 at 1:38 PM Joshua D. Drake  
wrote:
> I may be misunderstanding the question but:
Indeed...
> select cast(x/y as numeric(10,4));

Your answer is 2.0 instead of the correct 2.5 - you need to cast
before the division, not after.

David J.

Re: simple division

[David G. Johnston]

On Tue, Dec 4, 2018 at 1:57 PM Martin Mueller
 wrote:
>
> I didn't formulate my question properly, because the query went like
>  "select alldefects /wordcount"
> where alldefects and wordcount are integers.   But none of the different ways 
> of putting the double colon seemed to work.

IDK...the first thing that came to mind was to just stick it at the
end of the expression:

select x/y::numeric from (values (10,4)) vals (x,y)

And it worked...

If you want to propose a concrete documentation patch more power to
you but this doesn't come up enough to think that what we have is
materially deficient.  I'm sorry you are having trouble with it but
the lists do provide quick and customized answers for situations like
this.

David J.

Re: simple division

[Ron]

Use CAST() instead of ::.

SELECT CAST(alldefects AS NUMEREIC(10,4))/wordcount;

On 12/04/2018 02:57 PM, Martin Mueller wrote:

I didn't formulate my question properly, because the query went like
  "select alldefects /wordcount"
where alldefects and wordcount are integers.   But none of the different ways 
of putting the double colon seemed to work.

The Postgres notation of this simple procedure is very unintuitive. I haven't 
been able to remember several times, and most people think of me as a person 
with a reasonably good memory. There is no obvious place in the documentation 
to look this up.


On 12/4/18, 2:45 PM, "David G. Johnston"  wrote:

 On Tue, Dec 4, 2018 at 1:38 PM Joshua D. Drake  
wrote:
 > I may be misunderstanding the question but:
 Indeed...
 > select cast(x/y as numeric(10,4));
 
 Your answer is 2.0 instead of the correct 2.5 - you need to cast

 before the division, not after.
 
 David J.
 



--
Angular momentum makes the world go 'round.

Re: simple division

[Albrecht Dreß]

Am 04.12.18 21:57 schrieb(en) Martin Mueller:

I didn't formulate my question properly, because the query went like
 "select alldefects /wordcount"
where alldefects and wordcount are integers.


test=# create table xxx(alldefects bigint, wordcount bigint);
CREATE TABLE
test=# insert into xxx values (4, 10);
INSERT 0 1
test=# insert into xxx values (3, 17);
INSERT 0 1
test=# select alldefects::real / wordcount::real from xxx;
 ?column?
--
  0.4
 0.176471
(2 rows)

Hth,
Albrecht.

pgpZHLz289mhC.pgp
Description: PGP signature

Re: simple division

[Martin Mueller]

It worked, and I must have done something wrong. I'm probably not the only 
person who would find something like the following helpful:


division (integer division truncates the result)10/33
division (with decimal results) 10/3::numeric   3.
division (rounded)  round(10/3::numeric, 2) 3.33

From an end user's the question "how do I divide two integers and limit the 
number of decimals" is surely a common one. And  if you look it up somewhere, 
division is probably the search word.  Now you could argue that the user should 
already know about formatting and rounding.  But some don't.

If you wanted to look up a rare wordform in a famous early 20th century 
dictionary of Old English, you had to know the root form of the word. If you 
already knew the root form, there is a good chance that you didn't need to look 
it up in the first place. If you didn't know the root form, the dictionary was 
no use. 

In this, single stop shopping for the three most common problems of simple 
division makes life easier for users.







On 12/4/18, 3:06 PM, "David G. Johnston"  wrote:

On Tue, Dec 4, 2018 at 1:57 PM Martin Mueller
 wrote:
>
> I didn't formulate my question properly, because the query went like
>  "select alldefects /wordcount"
> where alldefects and wordcount are integers.   But none of the different 
ways of putting the double colon seemed to work.

IDK...the first thing that came to mind was to just stick it at the
end of the expression:

select x/y::numeric from (values (10,4)) vals (x,y)

And it worked...

If you want to propose a concrete documentation patch more power to
you but this doesn't come up enough to think that what we have is
materially deficient.  I'm sorry you are having trouble with it but
the lists do provide quick and customized answers for situations like
this.

David J.

Re: simple division

[Thomas Kellerer]

Martin Mueller schrieb am 04.12.2018 um 21:57:

I didn't formulate my question properly, because the query went like
  "select alldefects /wordcount"
where alldefects and wordcount are integers.   
But none of the different ways of putting the double colon seemed to

work.

One way is to make one of the integers a decimal by multiplying with 1.0

   select alldefects * 1.0 / wordcount


The Postgres notation of this simple procedure is very unintuitive. I
haven't been able to remember several times, and most people think of
me as a person with a reasonably good memory.


Postgres supports the SQL standard's CAST operator:

   select cast(alldefects as decimal) / wordcount

The "Postgres way" would be:

   select alldefects::decimal / wordcount


There is no obvious place in the documentation to look this up.


This is covered in the chapter "Type Casts"

https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

Re: simple division

[Rob Sargent]

On 12/4/18 2:36 PM, Martin Mueller wrote:

It worked, and I must have done something wrong. I'm probably not the only 
person who would find something like the following helpful:


division (integer division truncates the result)10/33


The math types might take offense here, with the use of "truncates".  
Integer division really ask how many times can one subtract the 
numerator from the denominator without going negative (or how many times 
does the numerator "go into" the denominator).


It may seem a nuisance, but int division is a useful construct and must 
be supported (and be the default). (If you have 10 people to transport 
in cars which hold four (all can drive) 10/4 = 3 ;) )

Re: querying both text and non-text properties

[Laurenz Albe]

Rob Nikander wrote:
> I’ve got an application where I’d like to search a collection of objects
> based on various properties, some text and others non-text (bools, enums,
> ints, etc). I’ve used full text search before, following the PG docs to
> set up a index on a ts_vector. And of course I’ve used normal indexes
> before for accelerating basic queries that use non-text columns.
> 
> Any recommendations on combining the two? For example, if I search for
> objects where the description text matches [some ts_query] and the
> color = red, I can imagine putting the color property into the text
> index somehow - maybe with tokens like: ’color_red’, ‘color_blue’,
> but for something like an integer … ?

You have two options:

A combined index:

  CREATE EXTENSION btree_gin;
  CREATE INDEX ON fulltext USING gin (to_tsvector('english', doc), color);

That is the perfect match for a query with

  WHERE color = 'red' AND to_tsvector('german', doc) @@ to_tsquery('english', 
'word');

But you can also create two indexes:

  CREATE INDEX ON fulltext USING gin (to_tsvector('english', doc));
  CREATE INDEX ON fulltext (color);

Then you don't need the extension, and PostgreSQL can still use them for the 
search,
either only one of them if the condition is selective enough, or a "BitmapAnd" 
of both.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Geographic coordinate values format conversion to DD (Decimal Degrees) format

[Paul Ramsey]

> On Dec 4, 2018, at 12:36 PM, Allan Kamau  > wrote:
> 
> Does PostgreSQL (more specifically PostGIS) have functions for these types of 
> conversions.
> 
> Below are examples of the geographic coordinates values I have coupled with 
> the resulting decimal degrees values.
> 39.529053 N 107.772406 W=39.5290530°, -107.7724060°
> 27.485973 S 153.190699 E=-27.4859730°, 153.1906990°
> 30°32’39” N, 91°07’36” E=30.5441667°, 091.127°
> 27.485973 S 153.190699 E=-27.4859730°, 153.1906990°
> 1¡20'1N 103¡45'15E=01.3336111°, 103.7541667°
SELECT st_astext(txt2geometry('S 20 10.8035165 W 176 36.074496'));
> =-20.1800586°, -176.6012416°
> 
> The "°" sign in the results is optional.


Nope, you’ve got a big ugly pattern matching problem there, unfortunately, and 
probably are going to have to regex your way out of the bag. PostGIS will help 
you output forms like that, but it doesn’t have any general handling of 
arbitrary DMS strings.

http://postgis.net/docs/manual-2.5/ST_AsLatLonText.html 


Here’s a PLPGSQL example that does half of your cases.

CREATE OR REPLACE FUNCTION txt2geometry(textcoord text)
RETURNS geometry AS 
$$
DECLARE 
textarr text[];
sep text;
lon float8;
lat float8;
BEGIN
textarr := regexp_matches(textcoord, '(\d+)(\D?)(\d{2})\D?([\d\.]+)\D? 
([NS]),? (\d+)\D?(\d{2})\D?(\d+)\D? ([EW])');
sep := textarr[2];
RAISE NOTICE '%', textarr;
-- DD.DD
IF sep = '.' THEN
lat := int4(textarr[1]) + int4(textarr[3]) / 100.0 + 
float8(textarr[4]) / pow(10, length(textarr[4])) / 100;
lon := int4(textarr[6]) + int4(textarr[7]) / 100.0 + 
float8(textarr[8]) / pow(10, length(textarr[8])) / 100;
-- DD.MM'SS"
ELSE
lat := int4(textarr[1]) + int4(textarr[3]) / 60.0 + 
float8(textarr[4]) / pow(10, length(textarr[4])) / 36;
lon := int4(textarr[6]) + int4(textarr[7]) / 60.0 + 
float8(textarr[8]) / pow(10, length(textarr[8])) / 36;
END IF;
IF textarr[5] = 'S' THEN
lat := -1 * lat;
END IF;
IF textarr[9] = 'W' THEN
lon := -1 * lon;
END IF;
RETURN ST_SetSRID(ST_MakePoint(lon, lat), 4326);
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;

Re: psql is hanging

[Ron]

On 11/30/2018 09:53 AM, John Smith wrote:
We have a long script of sql that we run, several thousand lines of sql. 
If I execute the script from start to finish, somewhere in the middle of 
it, one sql command will hang and take 2 to 3 hours. During this time, 
"htop" shows 100% cpu usage with a postgresql process, so it appears to be 
doing something.


If I stop that command, then immediately rerun the rest of the script 
starting from the command that "hung", the "hung" command then completes 
in 5 seconds and the rest of the script just continues on perfectly. I can 
interrupt the command within 30 mins of hanging, or 2 hours of hanging; 
regardless of when I interrrupt the hung command, it always immediately 
completes successully in < 5 seconds when I rerun the script immediately 
after that.


I have noticed this in multiple scripts, multiple commands; one was an 
"insert into", a different script hung on a "update" and a third one would 
hang on a "create table x as (select ... )". These are all short running 
commands normally, and we have indexes on all the columns for the 
appropriate "where" clauses.


I can't send the sql (forbidden), but the actual command does not appear 
to be the issue. Rerunning the same little section of sql manually which 
includes the hung command, always takes less than a minute, but the entire 
sql which should only take 30 minutes in total will always hang at the 
same spot; and then interrupting it (regardless of how long it was 
running) and rerunning immediately completes.


The "hung" command does eventually finish at times after 2 to 3 hours, 
most often we can't afford to wait for it to run since this batch of sql 
has to run daily.


Some other notes:
- Environment is Ubuntu 16.04; postgresql version was originaly 9.5(?) but 
we have upgraded to 11.1 using postgresql packages for ubuntu and the 
problem has persisted across postgresql versions.
- There are no other queries running on the postgresql server during this 
time, and server load is idling (other than whatever psql is doing).
- It is an amazon server, with ZFS as the files system, it has 50% disk 
free on this storage partition that postgresql database is on.

- We have 60 gigs of ram total, 8 cores.
- We have run the entire script using "create unlogged table" everywhere 
as well, no difference.
- We have made tunings to the postgresql configuration to make it use more 
RAM.
- All sql commands are sent through a custom Java application; so we use 
java + JDBC to connect to postgresql for this. The script has custom 
commands which are interpreted by the Java app, so we can't test running 
the exact script start to finish in psql native tools since the native 
tools won't understand our custom sql. However the java app is very 
simple. All individual sql commands are committed immediately after they 
run so there is no long transaction in play here. The psql jdbc driver is 
"postgresql-42.2.4".
- One may suspect the custom java app; but again, htop shows postgresql 
using 100% CPU usage on one core, so postgresql is chewing on the command, 
doing something.


I have attached our current postgresql.conf file.

We're kind of pulling out our hair here, any ideas?


The (apparent) solution for us was to upgrade the JDBC driver from 42.2.1 to 
42.2.5.  Note, though, that our giant script only does DDL statements.


--
Angular momentum makes the world go 'round.

Moving large table between servers: logical replication or postgres_fdw

[Rhys A.D. Stewart]

Greetings Folks,

I have a relatively large table (100m rows) that I want to move to a
new box with more resources. The table isn't doing anything...i.e its
not being updated or read from. Which approach would be faster to move
the data over:

  a). Use pg_fdw and do "create local_table as select * from foreign_table".
  b). setup logical replication between the two servers.

Regards,

Rhys
Peace & Love|Live Long & Prosper

Re: simple division

[Gavin Flower]

On 05/12/2018 10:51, Rob Sargent wrote:


On 12/4/18 2:36 PM, Martin Mueller wrote:
It worked, and I must have done something wrong. I'm probably not the 
only person who would find something like the following helpful:



division (integer division truncates the result)    10/3    3


The math types might take offense here, with the use of "truncates".  
Integer division really ask how many times can one subtract the 
numerator from the denominator without going negative (or how many 
times does the numerator "go into" the denominator).


It may seem a nuisance, but int division is a useful construct and 
must be supported (and be the default). (If you have 10 people to 
transport in cars which hold four (all can drive) 10/4 = 3 ;) )






Hmm...

10 / 4 = 2

Re: Moving large table between servers: logical replication or postgres_fdw

[Rob Sargent]

> On Dec 4, 2018, at 8:14 PM, Rhys A.D. Stewart  wrote:
> 
> Greetings Folks,
> 
> I have a relatively large table (100m rows) that I want to move to a
> new box with more resources. The table isn't doing anything...i.e its
> not being updated or read from. Which approach would be faster to move
> the data over:
> 
>  a). Use pg_fdw and do "create local_table as select * from foreign_table".
>  b). setup logical replication between the two servers.
> 
> Regards,
> 
> Rhys
> Peace & Love|Live Long & Prosper
> 
Can’t help but think an unread unwritten table goes to quietly to /dev/null :)

Re: Moving large table between servers: logical replication or postgres_fdw

[Rene Romero Benavides]

I tend to believe that a backup (pg_dump) in custom format (-F c) using
multiple jobs (parallel) -> restore (pg_restore) also with multiple
concurrent jobs would be better.

Am Di., 4. Dez. 2018 um 21:14 Uhr schrieb Rhys A.D. Stewart <
rhys.stew...@gmail.com>:

> Greetings Folks,
>
> I have a relatively large table (100m rows) that I want to move to a
> new box with more resources. The table isn't doing anything...i.e its
> not being updated or read from. Which approach would be faster to move
> the data over:
>
>   a). Use pg_fdw and do "create local_table as select * from
> foreign_table".
>   b). setup logical replication between the two servers.
>
> Regards,
>
> Rhys
> Peace & Love|Live Long & Prosper
>
>

-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Re: Moving large table between servers: logical replication or postgres_fdw

[Ron]

On 12/05/2018 12:05 AM, Rob Sargent wrote:

On Dec 4, 2018, at 8:14 PM, Rhys A.D. Stewart  wrote:

Greetings Folks,

I have a relatively large table (100m rows) that I want to move to a
new box with more resources. The table isn't doing anything...i.e its
not being updated or read from. Which approach would be faster to move
the data over:

  a). Use pg_fdw and do "create local_table as select * from foreign_table".
  b). setup logical replication between the two servers.

Can’t help but think an unread unwritten table goes to quietly to /dev/null :)


Sigh, no.

The legal and audit teams, and the customer, would be most upset if the data 
you're legally obligated to keep for 7 years suddenly disappears.


--
Angular momentum makes the world go 'round.

Re: Moving large table between servers: logical replication or postgres_fdw

[Ron]

On 12/04/2018 09:14 PM, Rhys A.D. Stewart wrote:

Greetings Folks,

I have a relatively large table (100m rows) that I want to move to a
new box with more resources. The table isn't doing anything...i.e its
not being updated or read from. Which approach would be faster to move
the data over:

   a). Use pg_fdw and do "create local_table as select * from foreign_table".
   b). setup logical replication between the two servers.


Or KISS and COPY WITH FORMAT BINARY.

--
Angular momentum makes the world go 'round.

Re: Moving large table between servers: logical replication or postgres_fdw

[Ron]

pg_backup/pg_restore will work, but parallel is pointless on a single 
(unpartitioned) table.


On 12/05/2018 12:13 AM, Rene Romero Benavides wrote:
I tend to believe that a backup (pg_dump) in custom format (-F c) using 
multiple jobs (parallel) -> restore (pg_restore) also with multiple 
concurrent jobs would be better.


Am Di., 4. Dez. 2018 um 21:14 Uhr schrieb Rhys A.D. Stewart 
mailto:rhys.stew...@gmail.com>>:


Greetings Folks,

I have a relatively large table (100m rows) that I want to move to a
new box with more resources. The table isn't doing anything...i.e its
not being updated or read from. Which approach would be faster to move
the data over:

  a). Use pg_fdw and do "create local_table as select * from
foreign_table".
  b). setup logical replication between the two servers.

Regards,

Rhys
Peace & Love|Live Long & Prosper




--
Angular momentum makes the world go 'round.

Re: simple division

[Rob Sargent]

> On Dec 4, 2018, at 9:33 PM, Gavin Flower  
> wrote:
> 
>> On 05/12/2018 10:51, Rob Sargent wrote:
>> 
>>> On 12/4/18 2:36 PM, Martin Mueller wrote:
>>> It worked, and I must have done something wrong. I'm probably not the only 
>>> person who would find something like the following helpful:
>>> 
>>> 
>>> division (integer division truncates the result)10/33
>> 
>> The math types might take offense here, with the use of "truncates".  
>> Integer division really ask how many times can one subtract the numerator 
>> from the denominator without going negative (or how many times does the 
>> numerator "go into" the denominator).
>> 
>> It may seem a nuisance, but int division is a useful construct and must be 
>> supported (and be the default). (If you have 10 people to transport in cars 
>> which hold four (all can drive) 10/4 = 3 ;) )
>> 
>> 
>> 
>> 
> Hmm...
> 
> 10 / 4 = 2
> 
And two are left stranded!
 The point is that integer math has its place. You cant have 2.5 cars. So 10/4 
in this context is 3. 
More correctly the calculation is 
10/4 + 10%4>0 ? 1 :0 = 3

(Maybe psql does have % so mod(10,4))

Re: Moving large table between servers: logical replication or postgres_fdw

[Rob Sargent]

L

> On Dec 4, 2018, at 11:13 PM, Rene Romero Benavides  
> wrote:
> 
> I tend to believe that a backup (pg_dump) in custom format (-F c) using 
> multiple jobs (parallel) -> restore (pg_restore) also with multiple 
> concurrent jobs would be better.
> 
>> Am Di., 4. Dez. 2018 um 21:14 Uhr schrieb Rhys A.D. Stewart 
>> :
>> Greetings Folks,
>> 
>> I have a relatively large table (100m rows) that I want to move to a
>> new box with more resources. The table isn't doing anything...i.e its
>> not being updated or read from. Which approach would be faster to move
>> the data over:
>> 
>>   a). Use pg_fdw and do "create local_table as select * from foreign_table".
>>   b). setup logical replication between the two servers.
>> 
>> Regards,
>> 
>> Rhys
>> Peace & Love|Live Long & Prosper
>> 
> 
> 
> -- 
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
> L
> 
Let’s compromise. Copy out as described. Tell the auditors where the file is. 
Skip the copy in. 
If you truly don’t need the data online going forward this might actually pass 
muster.