Re: [GENERAL] Retrieve the server's time zone

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

[GENERAL] Retrieve the server's time zone

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

[GENERAL] Logical replication + before trigger = ERROR: attempted to lock invisible tuple

FOR EACH ROW EXECUTE PROCEDURE customers_anonymize(); The "BEFORE INSERT" trigger seems to work fine. I found this Issue on 2ndQuadrant: https://github.com/2ndQuadrant/pglogical/issues/97 Is this on the radar? Is it already fixed? Thanks BR Thomas -- Sent via pgsql-general mailing l

Re: [GENERAL] idle in transaction, why

onds of "think time" between individual statements doesn't seem that unrealistic. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Why does a SELECT query cause "dirtied" buffers?

surprised why a SELECT (read-only) query would cause buffers to be dirtied? Under which situation does a SELECT query change a block? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [GENERAL] Monitoring Tool for PostgreSQL

Fabricio Pedroso Jorge schrieb am 18.10.2017 um 19:37: is there a "official" monitoring tool for PostgreSQL databases? For example, i come from Oracle Database, and there, we have Enterprise Manager to monitor and administrer the product... is there such a similar tool for PostgreSQL? There is

Re: [GENERAL] Making subscribers read only in Postgres 10 logical replication

an revoke those privileges for all tables in a schema: revoke insert,update,delete on all tables in schema public from the_user; You can do that for all future tables as well: alter default privileges in schema public revoke insert,update,delete on tables from the_user

[GENERAL] Postgres 10 manual breaks links with anchors

l? This also makes "cleaning" up links in e.g. StackOverflow that point to outdated versions of the manual a bit more cumbersome. Regards 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] Non-overlapping updates blocking each other

Melvin Davidson schrieb am 14.10.2017 um 17:32: More than likely, the optimizer has determined that a table scan is best, in which case it will use a table lock. That means one updates will be blocking each other. Since when does Postgres lock the whole table during an update? -- Sent

Re: [GENERAL] Multiple Schemas vs. Multiple Databases

that. Nowadays, cross-DB queries can quite easily be done using foreign tables (and they are quite efficient as well - much more efficient then dblink) Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [GENERAL] "Shared strings"-style table

Seamus Abshere schrieb am 13.10.2017 um 18:43: On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote: Theoretically / blue sky, could there be a table or column type that transparently handles "shared strings" like this, reducing size on disk at the cost of lookup overhead for all queries? (I

[GENERAL] Why does increasing the precision of a numeric column rewrites the table?

ter column some_number numeric(15,3); it takes quite a while (depending on the number of rows) which indicates a table rewrite is taking place. I don't understand why going from numeric(12,2) to numeric(15,3) would require a table rewrite. Thomas -- Sent via pgsql-general mailing list (pgs

Re: [GENERAL] Automatically check for anti-patterns in SQL queries

sqlcheck -h simply does nothing. The program briefly starts (I can see the title of my cmd.exe changed) but then exists immediately without even showing the help. Something like "sqlcheck -f test.sql" also shows no result at all (no error message, no output, nothing) Regards T

Re: [GENERAL] Final pg_dumpall should happen in Single-User-Mode

: In my case a short down-time is no problem. Regards, Thomas Am 20.09.2017 um 09:59 schrieb Thomas Güttler: Just for the records, I asked here the same question and got some replies: https://dba.stackexchange.com/questions/186045/how-do-i-prevent-changes-to-my-database Am 11.09.2017 um 15:25

Re: [GENERAL] VM-Ware Backup of VM safe?: snapshots: quiesced and non-quiesced

Thank you for "quiesced vs non-quiesced". I will ask the company providing the VM. Regards, Thomas Güttler Am 21.09.2017 um 03:48 schrieb Michael Paquier: On Wed, Sep 20, 2017 at 5:45 PM, Albe Laurenz <laurenz.a...@wien.gv.at> wrote: Thomas Güttler wrote: We run a Postg

Re: [GENERAL] libpq confusion

On Wednesday, September 20, 2017 1:47:05 PM EDT Igor Korot wrote: >Thx. >So it is referring to the command not a "command returning no data". ;-) assuming create table t (c int); select c from t; - PQresultStatus(result) == PGRES_TUPLES_OK - PQntuples(result) == number or

Re: [GENERAL] Performance appending to an array column

Paul A Jungwirth schrieb am 21.09.2017 um 23:05: but maybe I could write my own extension to load regular files into Postgres arrays, sort of getting the best of both worlds. There is a foreign data wrapper for that: https://github.com/adunstan/file_text_array_fdw but it's pretty old and

[GENERAL] VM-Ware Backup of VM safe?

We run a PostgreSQL 9.6 server in a virtual machine. The virtual machine is managed by the customer. He does backup the VM. Is this enough, is this safe? Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ I am looking for feedback: https://github.com/guettli

Re: [GENERAL] Final pg_dumpall should happen in Single-User-Mode

Just for the records, I asked here the same question and got some replies: https://dba.stackexchange.com/questions/186045/how-do-i-prevent-changes-to-my-database Am 11.09.2017 um 15:25 schrieb Thomas Güttler: I did a stupid mistake. I run the final pg_dumpall without switching to single-user

[GENERAL] Final pg_dumpall should happen in Single-User-Mode

gresql.org/docs/current/static/app-pg-dumpall.html How could the wording look like? I am not a native speaker Or do you have a better/different idea? Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ I am looking for feedback: https://github.com/guettli/programming-

Re: [GENERAL] What's the best way in postgres to use ANY() with LIKE '%'?

iven pattern. > You mean something like this? https://stackoverflow.com/q/46047339/330315 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] clustered index benchmark comparing Postgresql vs Mariadb

유상지 schrieb am 31.08.2017 um 04:03: > Cluster secondary indexes were faster than those without cluster indexes in > pg, but slower than mariadb. There is no such thing as a "clustered index" in Postgres. The Postgres "cluster" command physically sorts the rows of a table according to the sort

Re: [GENERAL] Serializable Isolation and read/write conflict with index and different keys

s. Sometimes simple SSI tests can show a lot of false positives just because of empty tables or missing statistics (ANALYZE). -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postg

[GENERAL] foreign key with char and varchar

data text); create table t8 (id varchar(7) primary key, data text, id_t7 char(7) references t7 (id)); I thought the columns referring and referenced had to be the same data type with the same length but it seems not to be the case. Thanks for answers Thomas

Re: [GENERAL] Isolation of schema renames

/message-id/flat/20170605191104.1442.24999%40wrigleys.postgresql.org -- Thomas Munro http://www.enterprisedb.com -- 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] Multixact members limit exceeded

On Thu, Aug 10, 2017 at 10:26 AM, Thomas Munro <thomas.mu...@enterprisedb.com> wrote: > eaten a total of n! member space with an average size of n/2 per Erm, math fail, not n! but 1 + 2 + ... + n. -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-general mailing li

Re: [GENERAL] Multixact members limit exceeded

building even larger ones. A thundering herd of worker processes repeatedly share-locking the same row or something like that? -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Logging in Code vs SQL-WHERE was: Row based permissions: at DB or at Application level?

or not. This is an argument for permission checking in code. On the other side I still think perm checking in SQL WHERE has more benefits. Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] Row based permissions: at DB or at Application level?

Am 27.07.2017 um 12:05 schrieb vinny: On 2017-07-27 10:27, Thomas Güttler wrote: Am 25.07.2017 um 12:59 schrieb vinny: On 2017-07-25 11:40, Thomas Güttler wrote: I would like to reduce the "ifing and elsing" in my python code (less conditions, less bugs, more SQL, more p

Re: [GENERAL] Row based permissions: at DB or at Application level?

Am 25.07.2017 um 12:59 schrieb vinny: On 2017-07-25 11:40, Thomas Güttler wrote: I would like to reduce the "ifing and elsing" in my python code (less conditions, less bugs, more SQL, more performance) Regards, Thomas Güttler A quick brainstorm: You could, probably... but

[GENERAL] Row based permissions: at DB or at Application level?

fing and elsing" in my python code (less conditions, less bugs, more SQL, more performance) How could an application which gets written from scratch use PostgreSQL to implement row based permissions? Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ -- Sent via p

[GENERAL] _page_cost parameter with values < 1

Hello, recently I have seen a Postgres configuration with the following values: seq_page_cost = 0.5 random_page_cost = 0.6 Is there any advantage (or maybe disadvantage) compared to using e.g. 1.0 and 1.2? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] What is exactly a schema?

marcelo schrieb am 14.07.2017 um 13:59: > Could I select a specific schema in the connection string? Say, by example > database=mydb.schemanumbertwo ? The JDBC driver does indeed support that: jdbc:postgresql://localhost/somedatabase?currentSchema=some_schema I think in the backround it

Re: [GENERAL] Concurrency and UPDATE [...] FROM

here the race would change the outcome, or some other serialisation scheme like table or advisory locks. [1] https://github.com/postgres/postgres/blob/master/src/backend/executor/README#L297 -- Thomas Munro http://www.enterprisedb.com -- 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] count records in two different table joined by

end as status_a , t2.id is null as test2exists from test1 t1 left join test2 t2 on t2.test1_id = t1.id hth Thomas

[GENERAL] Re: Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

e of "table" and "column" in the URI? You connect to a database, not to a table. 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] Download 9.6.3 Binaries

Andreas Kretschmer schrieb am 23.06.2017 um 20:58: I expected to find binaries for 9.6.3 at https://www.enterprisedb.com/download-postgresql-binaries but I only see 9.6.2. Am I looking at the wrong place? Yeah, use the community version from postgresql.org ;-) Regards, Andreas There are

Re: [GENERAL] sub-select with multiple records, columns

. json_object_agg() or json_agg() might be better suited for this. 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] pg_catalog tables don't have constraints?

kbran...@pwhome.com schrieb am 09.06.2017 um 20:57: Neil Anderson wrote: I've been exploring the pg_catalog tables and pointed a couple of tools at it to extract an ER diagram for a blog post. At first I thought it was a bug in the drawing tool but it appears that

Re: [GENERAL] dump to pg

a DataPump dump in Oracle or a "backup" in SQL Server), then the answer is no. Only Oracle or SQL Server can read those files. If they aren't too big, you might get away by installing the express edition of the respective DBMS, then import them using the native tools, then export the

Re: [GENERAL] 9.5 "chained equality" behavior

Joshua Ma schrieb am 30.05.2017 um 22:56: We're going to fix usages of this to instead do (a = b && a = c) instead of (a = b = c). That has to be (a = b AND a = c) The && operator is not valid for booleans -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

Adrian Klaver schrieb am 28.05.2017 um 17:51: After I finally found the EnterpriseDB Beta binaries (they are pretty well hidden) I tested with those, and everything works fine there. For those following along, where would that be? Here they are:

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

Tom Lane schrieb am 26.05.2017 um 20:18: I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL distribution) to upgrade a 9.6 cluster. pg_upgrade --check fails with the following messages: could not load library "$libdir/pgxml": ERROR: could not load library

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

Adrian Klaver schrieb am 26.05.2017 um 23:41: If that's coming from port/dynloader/win32.c, as I think it must be because the non-conformant-to-message-style-guidelines phrase "unknown error" appears nowhere else in our tree, then that's an error code that FormatMessage doesn't recognize.

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

ferent files. Well Thomas is using pg_upgrade from a BigSQL 10beta1 install to upgrade from a 9.6 instance of unknown provenance. pg_upgrade is complaining that the 9.6 cluster has pgxml.dll whereas the 10beta1 cluster does not even though xml2 is installed on both clusters. The question is t

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

Tom Lane schrieb am 26.05.2017 um 20:18: I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL distribution) to upgrade a 9.6 cluster. pg_upgrade --check fails with the following messages: could not load library "$libdir/pgxml": ERROR: could not load library

[GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

cognized collation provider: p In the database where pg_dump (v10) fails, I have created the following custom collation: CREATE COLLATION "de" (lc_collate = 'German_Germany.1251', lc_ctype = 'German_Germany.1251'); ALTER COLLATION "de" OWNER TO "thomas"; pg_dump (9.6) runs

Re: [GENERAL] logical replication in PG10 BETA

Igor Neyman schrieb am 22.05.2017 um 21:33: > Does built-in logical replication work on Windows in PG10 BETA release? > > I can’t make it working so far. > > I created Publication on “source” PG server, but when I’m trying to CREATE > SUBSCRIPTION… on “destination” server, I’m getting: > >

Re: [GENERAL] storing large files in database - performance

John R Pierce schrieb am 16.05.2017 um 16:44: > On 5/16/2017 7:35 AM, Thomas Kellerer wrote: >> When my (JDBC based) SQL client and the database server are on the same >> computer... > > node.js is Javascript, not java w/ jdbc I know that. I mentioned JDBC so that it's c

Re: [GENERAL] storing large files in database - performance

something about my methodology that is slowing things down? How far are your app server and the database server apart? Maybe it's just a case of a slow network 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] Insert performance and disk usage in JSON vs JSONB

Ronny Abraham schrieb am 15.05.2017 um 19:25: 4. Insert 10,000 rows to JSON, execution time (sec): 122.855001211 5. Insert 10,000 rows to JSONB, execution time (sec): 122.128999233 What’s interesting is that inserting to JSONB is slightly faster than inserting to JSON. A difference in

Re: [GENERAL] Top posting....

Tom Lane schrieb am 11.05.2017 um 19:43: > Bottom posting without trimming is just an awful combination: > whatever you do, don't do that. Amen to that. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Migration Query

ink about how you used the DATE data type in Oracle. If you rely on the time part, you have to translate that into a TIMESTMAP in Postgres. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailp

[GENERAL] Caching and Blobs in PG? Was: Can PG replace redis, amqp, s3 in the future?

Am 02.05.2017 um 05:43 schrieb Jeff Janes: On Sun, Apr 30, 2017 at 4:37 AM, Thomas Güttler <guettl...@thomas-guettler.de <mailto:guettl...@thomas-guettler.de>> wrote: Is is possible that PostgreSQL will replace these building blocks in the future? - redis (Caching) Po

Re: [GENERAL] Can PG replace redis, amqp, s3 in the future?

On April 30, 2017 1:37:02 PM GMT+02:00, "Thomas Güttler" <guettl...@thomas-guettler.de> wrote: >Is is possible that PostgreSQL will replace these building blocks in >the future? > > - redis (Caching) > - rabbitmq (amqp) > - s3 (Blob storage) T

Re: [GENERAL] Can PG replace redis, amqp, s3 in the future?

Am 30.04.2017 um 17:09 schrieb Bill Moran: > On Sun, 30 Apr 2017 13:37:02 +0200 > Thomas Güttler <guettl...@thomas-guettler.de> wrote: > >> Is is possible that PostgreSQL will replace these building blocks in the >> future? >> >> - redis (Caching) >&

Re: [GENERAL] Can PG replace redis, amqp, s3 in the future?

Am 30.04.2017 um 15:39 schrieb Thomas Delrue: > On April 30, 2017 1:37:02 PM GMT+02:00, "Thomas Güttler" > <guettl...@thomas-guettler.de> wrote: >> Is is possible that PostgreSQL will replace these building blocks in >> the future? >> >> - redi

[GENERAL] Can PG replace redis, amqp, s3 in the future?

void the other types of servers. The benefit is not very obvious on the first sight. I think it will saves you time, money and energy only in the long run. What do you think? Regards, Thomas Güttler -- I am looking for feedback for my personal programming guidelines: https://github.com/guettli/pr

Re: [GENERAL] browser interface to forums please?

an read them as a newsgroup provided by news.gmane.org - I do it that way. Thunderbird works quite well with that. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Suggestion to improve select pg_reload_conf()

t is being written to the logfile. To not break existing code (e.g. scripts that only expect true/false), this could be done through an optional boolean parameter (e.g. named "verbose"). Any thoughts? Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] Do I need to COMMIT an analyze statement?

David G. Johnston schrieb am 31.03.2017 um 16:49: On Friday, March 31, 2017, Thomas Kellerer <spam_ea...@gmx.net <mailto:spam_ea...@gmx.net>> wrote: Hello, something I have always wondered: If I run with autocommit turned OFF, do I need to commit an ANALY

[GENERAL] Do I need to COMMIT an analyze statement?

Hello, something I have always wondered: If I run with autocommit turned OFF, do I need to commit an ANALYZE statement, or is that "non-transactional"? Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscrip

Re: [GENERAL] Trigger based logging alternative to table_log

Jeff Janes schrieb am 27.03.2017 um 19:07: I have some code which uses table_log (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to selected tables. I don't use the restore part, just the logging part. It creates a new table for each table being logged, with several

Re: [GENERAL] Combining INSERT with DELETE RETURNING

Alexander Farber schrieb am 24.03.2017 um 16:06: > the doc https://www.postgresql.org/docs/9.6/static/sql-delete.html states: > > "The syntax of the RETURNING list is identical to that of the output list of > SELECT." > > So I am trying to rewrite the INSERT .. ON CONFLICT DO NOTHING followed

Re: [GENERAL] Incremental / Level -1 backup in PG

Rakesh Kumar schrieb am 22.03.2017 um 01:27: > PG does not have a concept of incremental backup. Postgres doesn't, but external tools can. e.g. Barman can do incremental backups: https://blog.2ndquadrant.com/incremental-backup-barman-1-4-0/ -- Sent via pgsql-general mailing list

Re: [GENERAL] CenOS 5/Postgresql 9.6

Steve Crawford schrieb am 17.03.2017 um 20:15: I'm aware of all those dates. Also that 9.6 has been out for six-months, and that RHEL/CentOS 5 are listed among the currently supported versions at https://yum.postgresql.org/. The question remains - does anyone know where I might find packages so

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

Alexander Farber schrieb am 02.03.2017 um 21:45: I am calling this stored function - CREATE OR REPLACE FUNCTION words_get_board( in_gid integer ) RETURNS TABLE ( out_bid integer, out_letters varchar[15][15], out_values

Re: [GENERAL] via psycopg2 or pg2pg? Move rows from one database to other

without a database adapter like psycopg2. Regards, Thomas Am 23.02.2017 um 17:40 schrieb Francisco Olarte: Thomas: On Thu, Feb 23, 2017 at 4:16 PM, Thomas Güttler <guettl...@thomas-guettler.de> wrote: Am 22.02.2017 um 16:00 schrieb Adrian Klaver: only written on a successful tr

Re: [GENERAL] Move rows from one database to other

table ... nice. -- Thomas Guettler http://www.thomas-guettler.de/ -- 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] Move rows from one database to other

Am 23.02.2017 um 10:33 schrieb Francisco Olarte: Thomas: On Wed, Feb 22, 2017 at 1:51 PM, Thomas Güttler <guettl...@thomas-guettler.de> wrote: I want to **move** the data. The data should get deleted on the satellite after transfer. I don't know how to delete the data which was copied,

Re: [GENERAL] Move rows from one database to other

Am 22.02.2017 um 16:00 schrieb Adrian Klaver: On 02/22/2017 04:51 AM, Thomas Güttler wrote: I have other concerns: atomar transaction. Movement should happen completely or not all. I don't think you can do this reliable (atomic transaction) with "copy table_name". You can i

Re: [GENERAL] Move rows from one database to other

satellite after transfer. I don't know how to delete the data which was copied, since inserts can happen during the copy statement. Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chang

Re: [GENERAL] Move rows from one database to other

... then there is a lag. That can happen (network outage), this is not very important. More important is that no single row gets lost or duplicated. Regards, Thomas -- Thomas Guettler http://www.thomas-guettler.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] Move rows from one database to other

involved. Versions are 9.5 and 9.6 I have other concerns: atomar transaction. Movement should happen completely or not all. I don't think you can do this reliable (atomic transaction) with "copy table_name". Regards, Thomas -- Thomas Guettler http://www.thomas-guettler.de/ -- Sent

Re: [GENERAL] Move rows from one database to other

of the rows should happen in background. Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ -- 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] Move rows from one database to other

Am 21.02.2017 um 15:12 schrieb Adrian Klaver: On 02/21/2017 12:53 AM, Thomas Güttler wrote: I want to move table rows from one database to an central database. You actually talking about moving from ~100 databases to the central database, correct? Both run PostgreSQL. Are all

[GENERAL] Move rows from one database to other

, since there a tons of possible race conditions: - inserts can happen during syncing. - Network can break during syncing. - inserts into the central table can break (e.g. disk full): No loss at the satellite database must happen. - ... How to solve this with PostgreSQL? Regards, Thomas

Re: [GENERAL] PostgreSQL on eMMC - Corrupt file system

Am 10.02.2017 um 09:16 schrieb Mark Morgan Lloyd: > On 09/02/17 23:00, Christoph Moench-Tegeder wrote: >> ## Thomas Güttler (guettl...@thomas-guettler.de): >> >>> Is running linux with postgres on eMMC a bad idea in general? >> >> I'd say that running any

Re: [GENERAL] Problems with Greatest

Teddy Schmitz schrieb am 16.02.2017 um 05:38: > As a quick follow up I just did an explain on the query, > > > Aggregate (cost=258007258.87..258007258.88 rows=1 width=8) > -> Nested Loop (cost=0.00..184292254.83 rows=14743000807 width=16) > -> Seq Scan on t1 (cost=0.00..3796.41 rows=263141

Re: [GENERAL] Can't restart Postgres

n 02/15/2017 09:45 AM, Shawn Thomas wrote: >> Which would you recommend? Leave the data directory in place and >> re-install PG or copy it to somewhere else, delete it and then >> re-install PG? > > I would copy the data directory somewhere else for safe keeping leaving t

Re: [GENERAL] Can't restart Postgres

Yes, definitely. > On Feb 15, 2017, at 9:49 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 02/15/2017 09:45 AM, Shawn Thomas wrote: >> Which would you recommend? Leave the data directory in place and >> re-install PG or copy it to somewhere else, delet

Re: [GENERAL] Can't restart Postgres

Yes, sadly it does explain things. Your insight has been super helpful though. -Shawn > On Feb 15, 2017, at 9:38 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 02/15/2017 09:28 AM, Shawn Thomas wrote: >> Well that would make more sense of things. I had remove

Re: [GENERAL] Can't restart Postgres

Which would you recommend? Leave the data directory in place and re-install PG or copy it to somewhere else, delete it and then re-install PG? -Shawn > On Feb 15, 2017, at 9:36 AM, Magnus Hagander <mag...@hagander.net> wrote: > > On Wed, Feb 15, 2017 at 6:28 PM, Shaw

Re: [GENERAL] Can't restart Postgres

get the database back in place. -Shawn > On Feb 15, 2017, at 9:01 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 02/15/2017 08:35 AM, Shawn Thomas wrote: >> Yes, that’s the correct sequence of scripts. And no there’s not anything >> really helpful in

Re: [GENERAL] Can't restart Postgres

directory into the newly installed one, will there be an xlog issue? -Shawn > On Feb 15, 2017, at 9:09 AM, Magnus Hagander <mag...@hagander.net> wrote: > > On Wed, Feb 15, 2017 at 6:03 PM, Shawn Thomas <thoma...@u.washington.edu > <mailto:thoma...@u.washington.edu>> wrote:

Re: [GENERAL] Can't restart Postgres

AM, Joshua D. Drake <j...@commandprompt.com> wrote: > > On 02/15/2017 08:35 AM, Shawn Thomas wrote: >> Yes, that’s the correct sequence of scripts. And no there’s not anything >> really helpful in the system logs. >> >> I’m thinking that at this point

Re: [GENERAL] Can't restart Postgres

15, 2017, at 6:35 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 02/14/2017 08:47 PM, Shawn Thomas wrote: >> No it doesn’t matter if run with sudo, postgres or even root. Debian >> actually wraps the command and executes some some initial scripts with >>

Re: [GENERAL] Can't restart Postgres

n Klaver <adrian.kla...@aklaver.com> wrote: > > On 02/14/2017 05:00 PM, Adrian Klaver wrote: >> On 02/14/2017 12:00 PM, Shawn Thomas wrote: >>> Yes that would be the standard approach. But the Debian package removes >>> pg_ctl from it normal place and wraps it with a perl

Re: [GENERAL] Can't restart Postgres

-o -c config_file="/etc/postgresql/9.4/main/postgresql.conf” -Shawn > On Feb 14, 2017, at 11:52 AM, Magnus Hagander <mag...@hagander.net> wrote: > > On Tue, Feb 14, 2017 at 8:47 PM, Joshua D. Drake <j...@commandprompt.com > <mailto:j...@commandprompt.com>> wrote: >

Re: [GENERAL] Can't restart Postgres

) Main PID: 28668 (code=exited, status=0/SUCCESS) CGroup: /system.slice/postgresql.service -Shawn > On Feb 14, 2017, at 11:31 AM, Joshua D. Drake <j...@commandprompt.com> wrote: > > On 02/14/2017 11:17 AM, Shawn Thomas wrote: >> I inadvertently deleted the ssl-cert-snakeo

[GENERAL] Can't restart Postgres

I inadvertently deleted the ssl-cert-snakeoil.pem out from under a running Postgres instance (9.4) which caused it to shut down. The last line of main.log: FATAL: could not load server certificate file "/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory I've since restored the

Re: [GENERAL] Use full text to rank results higher if they are "closer hit"

Excellent great info! To save the extra mailing list pings, thanks to _everyone_ this is exactly what I was looking for. Cheers, Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Use full text to rank results higher if they are "closer hit"

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

[GENERAL] Documentation inconsistency (at least to me)

that warrants this distinction). Thomas [1] https://www.postgresql.org/docs/current/static/functions-string.html [2] https://www.postgresql.org/docs/current/static/functions-array.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

[GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

Nikolai Zhubr schrieb am 13.02.2017 um 23:03: Maybe I should have been more specific. What I need is debugging/profiling pure communication side of server operation, implying huge lots of requests and replies going over the wire to and from the server within some continued (valid) session, but

Re: [GENERAL] Loose indexscan and partial indexes

Peter J. Holzer schrieb am 10.02.2017 um 14:02: > So it's doing a sequential scan on the initial select in the recursive > CTE, but using the index on the subsequent selects. > > But why? If it uses the index on > SELECT MIN(periodizitaet) FROM facttable_imf_ifs WHERE periodizitaet > 'x' >

Re: [GENERAL] PostgreSQL on eMMC - Corrupt file system

Am 08.02.2017 um 07:25 schrieb Thomas Güttler: > Hi PostgreSQL experts, > > ... # Update After following the hints from [this answer][1], I could sync via owncloud for hours, and no file system error occurs. This is no big surprise since now only very few io-operations happen on

[GENERAL] PostgreSQL on eMMC - Corrupt file system

rg/home/81-up-gws01w4g-memory32g-emmc-boardwo-vesa-plate.html # Question Is running linux with postgres on eMMC a bad idea in general? Or is my hardware broken? Regards, Thomas Güttler output of dmesg: [18471.780031] sdhci: Timeout waiting for Buffer Read Ready interrupt during tuning

Re: [GENERAL] Result of timestamp - timestamp

Adrian Klaver schrieb am 05.02.2017 um 22:40: >> I just stumbled about a report that has been running for a long time now >> and that relied on the fact that the interval "timestamp - timestamp" >> always returns an interval with days, hours, minutes. But never a >> "justified" interval with

[GENERAL] Result of timestamp - timestamp

stified" interval. So, my question is: will timestamp '2017-02-05 18:19:20' - timestamp '2016-11-18 23:00:00' always return "78 days 21:00:00"? Or is there any situation where the returned interval would be "2 mons 18 days 21:00:00" without using justiy_interval() on it. I co

  1   2   3   4   5   6   7   8   9   10   >