Re: [GENERAL] Postgresql.conf not found

Hi, Am 03.11.2017 um 12:51 schrieb Neto pr: But I'm not finding where the postgresql.conf file is. you can ask the database, inside psql: test=# show config_file; config_file - /etc/postgresql/10/main/postgresql.conf (1 Zeile) test=*#

Re: [GENERAL] Delete Duplicates with Using

Am 14.10.2017 um 08:20 schrieb Igal @ Lucee.org: Hello, I run the SQL query below to delete duplicates from a table. The subquery is used to identify the duplicated rows (row_num is a BIGSERIAL column). other solution, using the CTID-column: (rows with (1,1,1) and (5,5,5) are identical

Re: [GENERAL] Corrupt index

On 15 August 2017 21:54:56 GMT+02:00, Daryl Stultz wrote: > >>>We are using PostgreSQL 9.3.14 on a managed Amazon RDS with a > > >>Are you using reindex concurrently? There was a bugfix in 9.3.16 (+-1, >i'm not sure). > >Hi Andreas, not that I'm aware of. I issue "reindex table mytable" to >get o

Re: [GENERAL] Corrupt index

On 15 August 2017 21:24:29 GMT+02:00, Daryl Stultz wrote: >Hello, > >We are using PostgreSQL 9.3.14 on a managed Amazon RDS with a > Are you using reindex concurrently? There was a bugfix in 9.3.16 (+-1, i'm not sure). Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company --

Re: [GENERAL] cluster question

Am 15.08.2017 um 05:15 schrieb Alex Samad: Hi Quick question. I have a 2 node cluster - each node has its own ip. But from reading this, I really need a 3rd ip, which potentially floats between the nodes to which ever is the master / rw node. Is that right? Sort of makes sense I guess

Re: [GENERAL] cluster question

On 14 August 2017 08:39:54 GMT+02:00, Alex Samad wrote: >Hi > >I have setup a streaming replicating cluster, with a hot standby. > >Now I would like to change the RW to hot standby and change the hot >standby >to be the RW server. > >Is it just a matter of updating recover.conf file ? > >Alex I w

Re: [GENERAL] vacuum on streaming replication

On 31 July 2017 04:15:33 GMT+02:00, Alex Samad wrote: >Hi > >setup a cluster, with streaming replication and hot stand by > >the idea is to use the stand by to do queries whilst the primary is >doing >inserts. > >But I noticed the stats on the stand by server don't update, nor can I >run >vacuum a

Re: [GENERAL] Schemas and foreign keys

Am 21.07.2017 um 14:58 schrieb marcelo: Recently I asked regarding schemas, and received very useful answers. I conclude that I can put some tables in one schema and left others in the public one. If my app selects some schema, the backend will found automatically the absent tables in "public

Re: [GENERAL] Logging at schema level

Am 21.07.2017 um 08:11 schrieb Nikhil: Schema = tenant. So basically tenant level logging. On 21-Jul-2017 11:21 AM, "Andreas Kretschmer" <mailto:andr...@a-kretschmer.de>> wrote: On 21 July 2017 07:10:42 GMT+02:00, Nikhil mailto:nikhilsme...@gmail.com>> wrote:

Re: [GENERAL] Streaming Replication archive_command is really needed?

Am 21.07.2017 um 08:01 schrieb Michael Paquier: On Thu, Jul 20, 2017 at 10:07 PM, Leonardo M. Ramé wrote: El 20/07/17 a las 16:57, Andreas Kretschmer escribió: On 20 July 2017 21:46:09 GMT+02:00, "Leonardo M. Ramé" wrote: Hi, I wonder if archive_mode=on and archive_command par

Re: [GENERAL] Logging at schema level

On 21 July 2017 07:10:42 GMT+02:00, Nikhil wrote: >Hello, > >I am using postgresql schema feature for multi-tenancy. can we get >postgresql logs at schema level. Currently it is for the whole database >server (pg_log) > What do you want to achieve? Logging of data-changes per tenant? Regards, An

Re: [GENERAL] Backward compatibility

On 21 July 2017 04:13:47 GMT+02:00, Igor Korot wrote: >Hi, ALL, >According to the documentation PostgreSQL 9.6 (latest) supports > >CREATE INDEX IF NOT EXIST > >However, the version 9.4 and below supports only > >CREATE INDEX. > >Is there a query or a libpg function which can return the version of

Re: [GENERAL] Streaming Replication archive_command is really needed?

On 20 July 2017 21:46:09 GMT+02:00, "Leonardo M. Ramé" wrote: >Hi, I wonder if archive_mode=on and archive_command parameters in >postgresql.conf are really needed for streaming replication between two > >servers (master-slave). > >Regards, No. Andreas -- 2ndQuadrant - The PostgreSQL Suppor

Re: [GENERAL] RAM, the more the merrier?

Am 29. Juni 2017 16:19:41 MESZ schrieb Willy-Bas Loos : >Hi, > >We have a postgresql database that is now 1.4TB in disksize and slowly >growing. >In the past, we've had (read) performance trouble with this database >and >the solution was to buy a server that can fit the db into memory. It >had >0.5

Re: [GENERAL] Download 9.6.3 Binaries

Am 23. Juni 2017 20:04:39 MESZ schrieb "Igal @ Lucee.org" : >Hello, > >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? > >Thanks, > >Igal Sapir >Lucee Core Developer >Lucee.org

Re: [GENERAL] enable PostgreSQL SSL from RPM package installation

Am 22. Juni 2017 08:10:15 MESZ schrieb Dylan Luong : >Hi >I have installed PostgreSQL from the following RPMs onto the server. >And I would like to enable SSL. > >postgresql96-devel-9.6.2-2PGDG.rhel7.x86_64 >postgresql96-libs-9.6.2-2PGDG.rhel7.x86_64 >postgresql96-contrib-9.6.2-2PGDG.rhel7.x86_64 >

Re: [GENERAL] Streaming replication bandwith per table

Am 20. Juni 2017 03:06:05 MESZ schrieb Peter Eisentraut : >On 6/19/17 20:50, Maeldron T. wrote: >> > >Not easily. You could play around with pg_xlogdump to see what's going >on in the WAL. But even if you figure it out, there is not much you >can >do about it. > >Try perhaps logical replication

Re: [GENERAL] performance considerations of jsonb vs separate rows

Am 19. Juni 2017 21:29:40 MESZ schrieb Rob Nikander : > >I'm wondering about the tradeoffs, specifically: is it possible to >update one piece of a jsonb value without having to rewrite the entire >field? Updates in PostgreSQL are always Delete & Insert. So the answer is no. For session-data pl

Re: [GENERAL] effective_io_concurrency increasing

Am 19.06.2017 um 03:02 schrieb Patrick B: Hi guys. I just wanna understand the effective_io_concurrency value better. My current Master database server has 16 vCPUS and I use effective_io_concurrency = 0. What can be the benefits of increasing that number? Also, do you guys have any recom

Re: [GENERAL] storing postgres data on dropbox

Am 18.06.2017 um 03:03 schrieb Martin Mueller: This is a queestion from a Postgresql novice. I use Postgresql in a single-user environment on a Mac with OS Sierra. I use AquaFold DataStudio as a client, which is nice but also keeps me woefully ignorant about many aspects of the underlying ap

Re: [GENERAL] Repmgr + pgbouncer - Notification of master promotion to application level ...

Am 15.06.2017 um 11:57 schrieb Martin Goodson: The issues I think I would have with pgbouncer at the application level is ... 1) What if an application server is down when pgbouncer tries to update where the database IP is pointing to? When it is brought back into service could that crea

Re: [GENERAL] Repmgr + pgbouncer - Notification of master promotion to application level ...

Am 15.06.2017 um 08:26 schrieb Rory Campbell-Lange: On 15/06/17, Andreas Kretschmer (andr...@a-kretschmer.de) wrote: Am 15.06.2017 um 01:18 schrieb Martin Goodson: ...Do people setup pgbouncer nodes on the database servers themselves, on application servers, in the middle tier between the

Re: [GENERAL] Ordering of window functions with no order specified?

Am 15.06.2017 um 06:34 schrieb Ken Tanzer: Hi. If you use a window function and don't specify an order, will the rows be processed in the same order as the query results? In this particular case, I'm wondering about row_number(), and whether I explicitly need to repeat the ordering that's a

Re: [GENERAL] Repmgr + pgbouncer - Notification of master promotion to application level ...

Am 15.06.2017 um 01:18 schrieb Martin Goodson: I'm just wondering how people may have implemented this. Do people setup pgbouncer nodes on the database servers themselves, on application servers, in the middle tier between the application and database, and so forth, or some combination of

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

Am 13. Juni 2017 20:04:04 MESZ schrieb Dmitry O Litvintsev : > >I >wraparound)| 2017- >| t | enstore | autovacuum: VACUUM public.t_inodes (to prevent >wraparound)| 2017-06-13 12:31:04.870064-05 | >00:28:50.276437 | 40672 >chimera | t_inodes

Re: [GENERAL] Slow query plan used

Am 01.06.2017 um 14:07 schrieb Wetzel, Juergen (Juergen): Am 31.05.2017 um 13:27 schrieb Wetzel, Juergen (Juergen): Only 130 rows out of the 3 have ARCHIVED = 0 in this case i would suggest a partial index: create index on (archived) where archived = 0; Thanks, Andreas. Sorry for the

Re: [GENERAL] Slow query plan used

Am 31.05.2017 um 13:27 schrieb Wetzel, Juergen (Juergen): Only 130 rows out of the 3 have ARCHIVED = 0 in this case i would suggest a partial index: create index on (archived) where archived = 0; Thanks, Andreas. Sorry for the confusion about the table names. The hint with the partial

Re: [GENERAL] Slow query plan used

Am 30.05.2017 um 10:42 schrieb Wetzel, Juergen (Juergen): > I have a question concerning the query planner. I observe that chosen query plan differs on length and content of a like > search expression. We have a view combining data from two tables, both containing same number of rows (round a

Re: [GENERAL] COPY: row is too big

Am 26.05.2017 um 14:07 schrieb doganmeh: I tried varchar(12) also, nothing changed. My questions is 1) I have 672x12=8,064 characters in the first row (which are actually the headers), why would it complain that it is 8760. I am assuming here type `text` occupies 1 byte for a character. plea

Re: [GENERAL] Inheritance and foreign keys

Not possible - yet. Am 25. Mai 2017 13:48:59 MESZ schrieb Jayadevan M : >Hi, > >I designed three tables so that one table inherits another, and the >third >table references the parent table. If a record is inserted into the >third >table and the value does exist in the parent table indirectly, bec

Re: [GENERAL] Installing repmgr alongside PostgreSQL installed via EnterpriseDB installer instead of repositories?

Am 11.05.2017 um 15:58 schrieb Martin Goodson: Hello. First time poster, so please be gentle :) I have a PostgreSQL 9.6 database cluster running on a standalone Redhat 7.2 (Maipo) server. This may seem like a silly question. It probably *is* a silly question, so apologies in advance. Pos

Re: [GENERAL]

olumns AS cols where cols.table_schema = 'public' AND cols.table_name = 'abcatcol' ORDER BY cols.ordinal_position ASC; is this better? Regards, Andreas Kretschme? -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Tr

Re: [GENERAL] GENERAL - [How to check if the array contains the element.]

Am 26.04.2017 um 13:24 schrieb VENKTESH GUTTEDAR: Hello All, How do i check if any one element of one array exists in another array. Eg: array1 = [1,2,3,4] array2 = [1,4,5,7] Now i expect the result as true even if any one element matches from array2 in array1.

Re: [GENERAL] DROP INDEX CASCADE doesn't want to drop unique constraints?

nstraint: alter table employer drop constraint employer_employerid_key; Regards, Andreas Kretschmer -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- 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] How to create unique index on multiple columns where the combination doesn't matter?

1, c2, c3], 'asc'::text))=({1,2,3})« existiert bereits. test=*# (sorry for german messages, it means error, dublicate entry ...) Regards, Andreas Kretschmer -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via

Re: [GENERAL] controlled switchover with repmgr

ou can take care that there are no open transactions running on the master, without data loss. https://github.com/2ndQuadrant/repmgr/blob/master/README.md Regards, Andreas Kretschmer -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training &

Re: [GENERAL] Performance degradation when using auto_explain

Kisung Kim wrote: > And finally I found that auto_explain is the cause of the problem. real hardware or virtual hardware? On virtual there are sometimes problems with exact timings, please read: https://www.postgresql.org/docs/current/static/pgtesttiming.html Regards, Andreas Kretsch

Re: [GENERAL] pg_restore to a port where nobody is listening?

Am 21.12.2016 um 18:22 schrieb Daniel Westermann: Now I try to import into 9.6.1 => the instance is not running but the environment is set: postgres@pgbox:/home/postgres/ [PG961] netstat -na | grep 5439 postgres@pgbox:/home/postgres/ [PG961] echo $PGPORT 5439 postgres@pgbox:/home/postgre

Re: [GENERAL] huge table occupation after updates

ry (!) update creates a new row-version and marks the old row as 'old', but don't delete the old row. A Vacuum marks old rows as reuseable - if there is no runnung transaction that can see the old row-version. That's how MVCC works in PostgreSQL. Regards, Andreas Kretschmer -

Re: [GENERAL] Backup "Best Practices"

Am 23. November 2016 20:31:21 MEZ, schrieb John R Pierce : >On 11/23/2016 11:20 AM, Israel Brewster wrote: >> How do I determine which those are? Just based on the timestamp if >the >> WAL file, such that I could do something like take the timestamp of >> the last basebackup and delete all WAL

Re: [GENERAL] HA Cluster Solution?

On 10 October 2016 21:14:55 CEST, Periko Support wrote: >I'm trying to get better numbers, is a option in the table. >Meanwhile I reading some system performance numbers. >Yes odoo is strange sometimes. >But a cluster will be good for HA. >Thanks. > > Please identify the problematic queries (

Re: [GENERAL] Libpq functions & string to obtain connection parameters

037990/connecting-to-postgres-via-database-url-and-unix-socket-in-rails) Regards, Andreas Kretschmer -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@post

Re: [GENERAL] Large pg_xlog

On 27 September 2016 01:52:26 CEST, Francisco Reyes wrote: >Any ideas why pg_xlog is going so high? archive_command failed? If that happens the wal's will not deleted, you should see error-messages in the log. -- Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.

Re: [GENERAL] We have a requirement to downgrade from PostgreSQL 9.5.4 to 9.5.2

It is binary compatible, so yes. But trust me, there is problem with the new version. On 20 September 2016 06:00:59 CEST, KGA Official wrote: >Hi All, > >We are currently running at 9.5.2 and plan to upgrade to 9.5.4, but our >change requirement needs to plan for a downgrade with data >preservat

Re: [GENERAL] COL unique (CustomerID) plus COL unique (COUNT) inside CustomerID

3 1 > 3 2 > 3 3 > ... > > How to do this? don't store the Count-column and using row_number() over (partition by CustId) instead? Btw.: Greetings, how are you? ;-) Regards, Andreas Kretschmer -- Andreas Kretschmer htt

Re: [GENERAL] PG vs ElasticSearch for Logs

. Do you know BRIN? So, in your case, consider partitioning, maybe per month. So you can also avoid mess with table and index bloat. Greetings from Dresden to Chemnitz (is this still valid?) Regards, Andreas Kretschmer -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Devel

Re: [GENERAL] PK Index - Removal

Yes, you can drop the unused index. On 11 August 2016 05:30:15 CEST, Patrick B wrote: >Hi guys, > >I got the following index: > >CREATE INDEX "ix_mo_pk" ON "mo" USING "btree" ((("id")::"text")) > > >The "ID" is my primary key: > >> "mo_pkey" PRIMARY KEY, "btree" ("id") > > >The ix_mo_pk index is

Re: [GENERAL] WAL directory size calculation

Am 28.07.2016 um 15:25 schrieb Moreno Andreo: Hi folks! :-) I'm about to bring up my brand new production server and I was wondering if it's possible to calculate (approx.) the WAL directory size. I have to choose what's better in terms of cost vs. performance (we are on Google Cloud Platform)

Re: [GENERAL] Streaming replication failover process - Pgsql 9.2

Am 10.07.2016 um 23:19 schrieb Patrick B: Hi all, There will be a network maintenance at the company where my servers are... I've got one master and one slave server, running PostgreSQL 9.2. As the network will be down, the internet won't be working as well as the intranet. Both servers won

Re: R: [GENERAL] Vacuum full: alternatives?

Am 20.06.2016 um 11:43 schrieb Job: Hi Andreas, I would suggest run only autovacuum, and with time you will see a not more growing table. There is no need for vacuum full. So new record, when will be pg_bulkloaded, will replace "marked-free" location? exactly, that's the task for vacuum

Re: [GENERAL] Vacuum full: alternatives?

Am 20.06.2016 um 11:18 schrieb Job: Hello, we have a table with an heavy traffic of pg_bulkload and delete of records. The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back. We have important problems on size and the only way to gain free space is issueing a vacuum ful

Re: [GENERAL] Keeping top N records of a group

ds like a case for window-functions here (maybe row_number() or rank()), please read that for the start: http://stackoverflow.com/questions/7613785/postgresql-top-n-entries-per-item-in-same-table Regards, Andreas Kretschmer -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Develop

Re: [GENERAL] pg_largeobject

segsize ( throwing error > "configure: error: Large file support is not enabled. Segment size cannot be > larger than 1GB" ) check if your filesystem supports large files: getconf FILESIZEBITS /some/path If the result is 64, LFS is supported. Regards, Andreas Kretschmer -- Andre

Re: [GENERAL] Multi Master Replication setup

Sachin Srivastava wrote: > Dear Concern, > > Kindly inform to me how to setup multi master replication in Postgres. i think, you are looking for BDR. Please use google for more details. Regards, Andreas Kretschmer -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL De

Re: [GENERAL] Insert data in two columns same table

that for insert into the 3rd table. And i think, you are looking for an update, not insert. So you have to define how your tables are linked together (join). Can you explain how these tables are linked together? -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- 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 data in two columns same table

r read a good book on it at least.. > > > the *CORRECT* SOLUTION WOULD BE MORE LIKE yepp, full ack. -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@

Re: [GENERAL] Insert data in two columns same table

T 0 9 test=*# select * from destination ; s1 | s2 + 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 2 | 3 3 | 1 3 | 2 3 | 3 (9 rows) That's all, or? Keep in mind: you have N * M different combinations from the 2 tables. -- Andreas Kretschmer http://www.2ndQuadrant.c

Re: [GENERAL] Question about shared_buffer cache behavior

ition reads other rows than the first query. Keep in mind: a index search reads the index and pulls the rows that matched the condition from the heap, no more. Regards -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [GENERAL] "PostgreSQL" Version 9.3 Supportability

ell. You can also use the latest Version 9.5 on this plattform, and i suggest that. -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- 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] Does RAISE EXCEPTION rollback previous commands in a stored function?

> Alexander Farber hat am 1. März 2016 um 19:41 > geschrieben: > > > Good evening, > > in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous > commands in a stored function? Yes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] CONCAT returns null

Adrian Klaver wrote: >> >> NULL concat with a value returns NULL. You can avoid that using >> COALESCE(value, ''), that returns the value, or, if the value NULL, ''. > > > http://www.postgresql.org/docs/9.5/interactive/functions-string.html > " > concat(str "any" [, str "any" [, ...] ]) text

Re: [GENERAL] CONCAT returns null

Sterpu Victor wrote: > Hello > > I have this concat: > CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, '-MM-DD'), f2.nrfo, TO_CHAR > (fd7.validfrom, '-MM-DD'), DATE(fd5.validto)-DATE(fd1.validfrom)) > that works fine but when I change to this(I added a ' with '): > ARRAY_AGG(CONCAT(f.nrfo,

Re: [GENERAL] Select specific tables in BDR

> Kaushal Shriyan hat am 23. Februar 2016 um 11:43 > geschrieben: > > > Hi, > > Is there a option of selecting tables in BDR which is a Multi Master PG > Replication (http://2ndquadrant.com/en/resources/bdr/). > > I mean if i have let's say t1,t2,t3,t4 and t5. Can i have BDR only for > t1,t

Re: [GENERAL] Live steraming replication setup issue!

any error on DR server but when I try to > run any psql on DR, it throwing up below error. > > psql: FATAL: the database system is starting up please show us your recovery.conf. this should include this line: standby_mode = 'on' -- Andreas Kretschmer http://www.2

Re: [GENERAL] Cannot start the PostgreSQL service

David Unsworth wrote: > > This was working until recently. In Services I right clicked on properties > and > I think I changed the METHOD in pg_hba.conf from md5 to trust. > I think after making this change the problems started. > > In Services I cannot start the PostgreSQL Server 8.4 due t

Re: [GENERAL] Replication Question

Bala Venkat wrote: > Hi there - > >    We have a set up where there is One master streaming to 3 Slaves .  > 2 slaves are in our DR environment. One is the prod environment.  > >   Wanted to make the DR as primary. I know we can make the one of the > slave in DR to primary. If I

Re: [GENERAL] Performance options for CPU bound multi-SUM query

Matt wrote: > I have a warehousing case where data is bucketed by a key of an hourly > timestamp and 3 other columns. In addition there are 32 numeric columns. The > tables are partitioned on regular date ranges, and aggregated to the lowest > resolution usable. > > The principal use case is to

Re: [GENERAL] How can i add a postgresql connection using inno setup?

Just do it. What's the Problem? Errors? On 18 January 2016 08:31:33 CET, yang wrote: >Hi all > >I want to know how can i add a connection using inno setup program like >below picture. > > > > >In my program, I should add databases to localhost

Re: [GENERAL] master slave failover - secondary slaves

Steven Livingstone wrote: > Hi all, I am relatively new to Postgres but after some some work master/slave > replication and failover working. > > I can use a trigger file to promote my first slave to a new master but where I > am confused (from reading various docs) is quite how the second, thir

Re: [GENERAL] PostgreSQL upgrade 9.3.4 -> 9.3.10

Dev Kumkar wrote: > Hello, > > > I want to upgrade my database from version 9.3.4 to 9.3.10. > > For this task, do I need to upgrade database using pg_upgrade utility? > http://www.postgresql.org/docs/9.3/static/pgupgrade.html No. > Can someone please provide more details here and also what

Re: [GENERAL] Support for BDR in 9.5?

Afaik no, you have to use 9.4. Am 8. Januar 2016 18:39:07 MEZ, schrieb "Andrew Biggs (adb)" : >Can anyone tell me if PostgreSQL 9.5 supports (either natively or by >extension) the BDR functionality? > >I tried it out and ran into issues, but it could well have been I was >doing something wrong. >

Re: [GENERAL] select and count efficiency (~35 mln rows)

Grzegorz Kuczera wrote: > This is my first question here, so I would like to say hello to everyone:) > > In my case, the problem appears when I want to fetch some data to inflate the > table with it. First of all, I am counting the records from the table (for > paging, over indexed column), then

Re: [GENERAL] Streaming replication stacked.

Michael Paquier wrote: > As Andreas has already outlined, as hot_standby_feedback is enabled > the master has to wait for the slave and the slave cannot replay from > the master as transactions are running on the hot standby. Oh, thanks for the confirmation ;-) (wasn't really sure) Andreas --

Re: [GENERAL] Streaming replication stacked.

Andreas Kretschmer wrote: > ok, i think it's clear now: because of running transactions on the > standby (and hot_standby_feedback on) the master has to wait for the > slave and can't replay all from the master. Mhh. Maybe i'm wrong, can't reproduce that. Andre

Re: [GENERAL] Streaming replication stacked.

Yoji wrote: > Hi, Andreas > > Thank you for replying. > > You're right, I have only 1 slave. > > And I need running transactions on slave. > Once I restarted postgres service on slave and then process began to move. ok, i think it's clear now: because of running transactions on the standby (

Re: [GENERAL] A unique pairs version of UNNEST() ?

Andy Colson wrote: >> So in an example where list_of_ids is {1,2,3,4,5} I would essentially get: >> >> {1, 2} >> {1, 3} >> {1, 4} >> {1, 5} >> {2, 3} >> {2, 4} >> {2, 5} >> {3, 4} >> {3, 5} >> {4, 5} >> >> >> Any tips? Thanks! >> >> -- >> Wells Oliver >> wells.oli...@gmail.com

Re: [GENERAL] Streaming replication stacked.

Yoji wrote: > Hi, Andreas > > Thank you for reply. > > Below is the recovery.conf. > > -- > standby_mode = on > primary_conninfo = 'host=172.16.xxx.xxx user=xx' > trigger_file = '/tmp/trigger_file0' >

Re: [GENERAL] Streaming replication stacked.

y-tsuk...@xseed.co.jp wrote: > - > > Do you have any solution? please show us your recovery.conf. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvald

Re: [GENERAL] Transfer db from one port to another

Killian Driscoll wrote: > It worked - thank you very much for your time. Great! > Regarding the file format used: I had used the pg_dump with .sql, but you > suggested .out. Is there a particular reason to use .out instead of .sql when > backing up? No, doesn't matter. Andreas -- Really,

Re: [GENERAL] Calling function (table_name, schema_name) within event trigger

Susan Hurst wrote: > h...well, Tom, at least you saved me a lot of frustration with > trying to get this to work :-) For the time being, I'll just follow up > DDL activity with a procedure that compares diffs between > information_schema and the history tables. If and when pl/pgsql of

Re: [GENERAL] Options for complex materialized views sharing most of the same logic?

23:04:07 MEZ, schrieb Wells Oliver : >I do not. I just probably tweak it a couple of times a week due to >adding/removal of columns of interest and I just would like to have the >logic in one place if possible... > >On Sun, Dec 27, 2015 at 4:03 PM, Andreas Kretschmer >> wrote

Re: [GENERAL] Options for complex materialized views sharing most of the same logic?

Do you recreate the views every day? Why? (stupid smartphone-app, sorry for top-posting) Am 27. Dezember 2015 22:39:58 MEZ, schrieb Wells Oliver : > >Is there some easier way for me to maintain the structure of the view >without copying/pasting it 4 times and making one small tweak? I find >myse

Re: [GENERAL] Calling function (table_name, schema_name) within event trigger

> 9.4 did not offer very complete facilities for finding out what the > DDL command had done; 9.5 will provide more info.) > > regards, tom lane Really? http://www.postgresql.org/docs/9.5/static/plpgsql-trigger.html still contains only TG_EVENT and TG_TAG for Triggers on E

Re: [GENERAL] Calling function (table_name, schema_name) within event trigger

> Melvin Davidson hat am 27. Dezember 2015 um 19:55 > geschrieben: > > > It's kind of difficult to figure out what is going on. Apparently, the > function that is called "store.add_history_master()" thinks tg_table_name > is a COLUMN in a table, as evidenced by > "ERROR: column "tg_table_name"

Re: [GENERAL] grep -f keyword data query

> Tom Lane hat am 27. Dezember 2015 um 19:11 geschrieben: > > > Hiroyuki Sato writes: > > I would like to create the query like the following. > > It work well, but extreme slow. > > ... > > Explain output. > > > Nested Loop (cost=0.45..1570856063.28 rows=5712200 width=57) > >-> I

Re: [GENERAL] grep -f keyword data query

Hiroyuki Sato wrote: > Hello. > > I would like to create the query like the following. > It work well, but extreme slow. > Is it possible to improve this query like the command ``grep -f keyword > data``? > > What kind of Index should I create on url_lists table? can you show us the create ta

Re: [GENERAL] Strange syntax with select

Edson F. Lidorio wrote: > Hello, > I excuse my ignorance with SQL and my English. > I wonder if these procedures are correct or is it a bug? It's not a bug, but storing the result in a new table is senseless. Why do are doing that? Andreas -- Really, I'm not out to destroy Microsoft. That wi

Re: [GENERAL] Transfer db from one port to another

Charles Clavadetscher wrote: > Hello > > > > Why don’t you simply change the port in postgresql.conf and restart the > server? i think, he wants the data from the one database within the other, because of that he can't change the port. Andreas -- Really, I'm not out to destroy Microsoft.

Re: [GENERAL] Transfer db from one port to another

Killian Driscoll wrote: > I am using Windows 8 64 bit, with postgreSQL 9.3 on port 5432 and postgreSQL > 9.4 on port 5532 with the latter set up to use with Bitnami stack to test php > files I am generating from my db. > > I want to transfer my db with three schemas from port 5432 to port 5532 t

Re: [GENERAL] Unique index problem

Andreas Kretschmer wrote: > Andreas Kretschmer wrote: > > > > > Maybe there are better solutions, it's a quick hack ;-) > > better solution: sorry, doesn't work =:( Andreas -- Really, I'm not out to destroy Microsoft. That will just be

Re: [GENERAL] Unique index problem

Andreas Kretschmer wrote: > > Maybe there are better solutions, it's a quick hack ;-) better solution: test=*# create unique index on foo(a,b,c) where a is not null and b is not null and c is not null; CREATE INDEX (partial index) Andreas -- Really, I'm not out to destroy

Re: [GENERAL] Unique index problem

Sterpu Victor wrote: > Hello > > I created a unique index that doesn't seem to work when one column is NULL. > Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON > lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, > id_lab_sample_types); > Now I

Re: [GENERAL] Moving Postgresql 9.1 instance from FreeBSD 9.0 machine to FreeBSD 10.2

Read the doc about Replikation. I think you can simply set up the New machine AS streaming Replikation slave and promote it than AS master - with no downtime. Am 20. Dezember 2015 02:50:57 MEZ, schrieb Amitabh Kant : >Hi > >I have a Postgresql 9.1 instance running on FreeBSD 9.0 (64 bit). The >ma

Re: [GENERAL] Error promoting slave on cascading replication using replication slots

Alvaro Melo wrote: > > I found a instruction to add the following line to recovery.conf: > recovery_target_timeline = 'latest' > > When this line is added, slave2 keeps its replication with slave 1: > 2015-12-17 13:37:54 BRST [868-2] LOG: replication terminated by primary > server > 2015-12-17

Re: [GENERAL] BDR

Craig Ringer wrote: > On 15 December 2015 at 16:49, Andreas Kretschmer > wrote: > > > > BDR is currently an addon for 9.4, I don't believe its available for 9.5 > > yet. > > apparently, thx for the answer. > > > Correct, there's

Re: [GENERAL] BDR

John R Pierce wrote: > On 12/15/2015 12:01 AM, Andreas Kretschmer wrote: >> I would like to play with BDR, can i use my 9.5 / 9.6 installation >> (first attempt fails) or do i have to use 9.4 stable? > > 9.5 is a in-development version, 9.6 doesn't even exist, why

[GENERAL] BDR

Hi @ll, I would like to play with BDR, can i use my 9.5 / 9.6 installation (first attempt fails) or do i have to use 9.4 stable? Regards, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds)

Re: [GENERAL] Loggingt psql meta-commands

oleg yusim wrote: > Greetings! > > I'm new to PostgreSQL, working on it from the point of view of Cyber Security > assessment. In regards to the here is my question: > > Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp, > \z, etc? start psql with -E Andreas -- R

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

> FattahRozzaq hat am 10. Dezember 2015 um 01:27 > geschrieben: > > > Hi John, > > I really don't know why I should keep the wal archives. That's the problem! But that's your part, not our. If you need a Backup with PITR-capability you have to create a so called basebackup and continously W

Re: [GENERAL] Interrupted streaming replication

Melvin Davidson wrote: > As long as you have 00010089002C and the subsequent WALs in your > archive directory, then you should > be able to simply scp them to you second slave's pg_xlog directory. Nice idea ;-) wasn't sure if that works, but yes, nice. Andreas -- Really, I'm not

  1   2   3   4   5   >