Re: [GENERAL] How to exclude blobs (large objects) from being loaded by pg_restore?

his isn't a solution. i HAVE a complete dump, but now i need a restore without the lo - data. And - you can't create a new dump with -T for several reasons now. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect.

Re: [GENERAL] My index doesn't write anymore but read

new data are really slow. please provide us a explain . Please try a 'analyse '. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with

Re: [GENERAL] My index doesn't write anymore but read

ould) run it after mass data changes. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, S

Re: [GENERAL] Optimizing a read-only database

> François Battail hat am 18. Mai 2015 um 16:07 > geschrieben: > > > Dear List, > > I would like to know if somebody is aware of tricks for optimizing > PostgreSQL settings for a read-only database. you can set fillfactor to 100 alter table ... set (fillfactor = 100), see http://www.postg

Re: [GENERAL] Between with a list of ranges possible ?

Arup Rakshit wrote: > Hi, > > Can I do the below 3 queries in a single query ? OR ? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would rec

Re: [GENERAL] replacing jsonb field value

27;t find how it works. Thx. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Ger

Re: [GENERAL] replacing jsonb field value

lion/jsonbx), I also had > a go (here: https://github.com/glynastill/pg_jsonb_opx). Thanks. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would re

[GENERAL] Find out what on what function depends an index

. So the idea is: Have an event trigger ON ddl_command_end to do a REINDEX; see the attached example. The question is the todo in the script: Is there a way to find out what indexes depends on what function? thanks Andreas /* $Id: t_reindexfn.sql,v 1.1 2015/05/17 08:42:31 andreas Exp $ */ \set

Re: [GENERAL] Find out what on what function depends an index

On 01.06.2015 16:15, Tom Lane wrote: Andreas Ulbrich writes: The question is the todo in the script: Is there a way to find out what indexes depends on what function? Direct dependencies would show up in pg_depend. Indirect ones wouldn't, since we don't analyze function bodies t

[GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

Salvete! I'm in a handle for a trigger for TRUNCATE. Is it possible to find out whether the TRUNCATE TABLE ist called with CASCADE? regads andreas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mai

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

;mailto:laurenz.a...@wien.gv.at>> wrote: Andreas Ulbrich wrote: > I'm in a handle for a trigger for TRUNCATE. Is it possible to find out > whether the TRUNCATE TABLE ist called with CASCADE? I don't think there is. But you can find out the table where

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

, Andreas Ulbrich <mailto:andreas.ulbr...@matheversum.de>> wrote: On 02.06.2015 16:20, Melvin Davidson wrote: You can use the following to list the triggers and see what functions they call. Then you can check pg_proc to see how TRUNCATE is used in prosrc. SELECT

[GENERAL] ALTER EVENT TRIGGER as non superuser

EXECUTE PROCEDURE event_trigger_function(); -- disable trigger and change the function again, index is corrupt; reindex \dy+ a_ddl_trigger ALTER EVENT TRIGGER a_ddl_trigger DISABLE; SELECT SESSION_USER, CURRENT_USER; \du andreas \dy+ a_ddl_trigger ROLLBACK; \set ON_ERROR_ROLLBACK i

Re: [GENERAL] cannot connect to database through ECPG but can through psql

t access the database.  I get errors: My guess: psql connects via unix socket, that's works. The programm tries to connect via tcp/ip - don't work. Check if listen_adresses in your postgresql.conf is enabled. Andreas -- Really, I'm not out to destroy Microsoft. That will just b

Re: [GENERAL] Error prone compilation of stored procedure

pinker wrote: > What's the reason behind very "tolerant" error checking during stored > procedure compilation? they are not compiled but interpreted at runtime. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely

[GENERAL] [9.5] question about row level security

can't revoce the update-priv for this column. create policy enable_boss on for update using (boss=current_user) with check (???) Is there a way to access old.* and new.* like within a trigger? (something like with check(old.col_privat=new.col_privat)) Andreas -- Really, I'm

[GENERAL] [9.5] next question: rls and indexes

ap Scan on rls_test (cost=4.97..232.90 rows=1 width=335) Recheck Cond: (name = ("current_user"())::text) Filter: (name = ("current_user"())::text) -> Bitmap Index Scan on idx_name (cost=0.00..4.97 rows=91 width=0) Index Cond: (name = ("current_user&

Re: [GENERAL] Rounding Float Array

-- {5.3443,100.2946} {5.3886,100.296} {3.1655,101.6092} {5.3766,100.3147} (4 rows) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with

Re: [GENERAL] Rounding Float Array

hubert depesz lubaczewski wrote: > > Is there an easy way to round all values to 4 decimals. > > Sure: > > (depesz@[local]:5960) 12:15:46 [depesz] > $ select geoloc::numeric(8,4)[] from alex; > geoloc > --- > {5.3443,100.2946} Nice! Andr

Re: [GENERAL] postgres standby won't start

an use pg_basebackup, it's well documented. For the future: increase wal_keep_segments and/or use physical replications slots, if you are using 9.4 or higher. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect.

Re: [GENERAL] There can be only one

Create a partial unique index on is_default. Am 11. Oktober 2015 09:41:08 MESZ, schrieb Jason Dusek : >Consider a table of providers, for which one is the default. For >example, >payment providers: > >CREATE TABLE payment_via ( > iduuid PRIMARY KEY, > provider text NOT NULL, >

Re: [GENERAL] There can be only one

Andreas Kretschmer wrote: > Create a partial unique index on is_default. as an example: test=# CREATE TABLE payment_via ( idint PRIMARY KEY, provider text NOT NULL, keys hstore NOT NULL DEFAULT '', is_defaultboolean NOT NULL DEFAULT FALSE );

[GENERAL] problems with copy from file

:00:48:22 +0100] "\x16\x03\x01\x01\xb1\x01" 501 10..." I have a solution: test=*# \copy apache_log (data) from program 'sed -e "s/\\x/x/g" test.log'; COPY 5 The table contains the corrent content: ... other-domain.de bb.243.xx.yyy - - [06/Nov/2014:00:48:22 +0

Re: [GENERAL] postgresql 9.4 streaming replication

Do you have a client with a propper recovery.conf running? Am 19. Oktober 2015 09:58:40 MESZ, schrieb "Sven Löschner" : >I have the following problem: i am trying to set up a streaming >replication scenario with load balancing. I read various tutorials but > >i cannot find the mistake. The repl

Re: [GENERAL] Returning JSON or JSONB

protocol level. If you are returning JSON, so is JSON the best choice. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly.

Re: [GENERAL] temporary indexes?

heaper do the nalytics/reporting without an index. And yes: you can create an indexe concurrently. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recom

Re: [GENERAL] Vacuum full: alternatives?

space as free, but don't give the space back to os. I would suggest run only autovacuum, and with time you will see a not more growing table. There is no need for vacuum full. Andreas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscri

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

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: [GENERAL] WAL directory size calculation

Without Replication 1 GB would be fine, even with replication. But it must be realible! Andreas -- 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] 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] 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] 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] 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] 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] 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] HA Cluster Solution?

fy the problematic queries ( for instance using pg_stat_statements) and send this to the team behind odoo. Andreas. -- Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] Query generator

th --dry-run | grep your_table_name Thanks to Julien, sqlsmith's master branch now has an option --exclude-catalog that inhibits use of catalog relations. If you are building from github, you might want to give it another chance with this option. I'll probably do a proper release in next tw

[GENERAL] Change column type from int to bigint - quickest way

till on 9.1 unfortunately - upgrade is going to follow soon after this. Thanks! Andreas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Change column type from int to bigint - quickest way

till on 9.1 unfortunately - upgrade is going to follow soon after this. Thanks! Andreas -- 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] Change column type from int to bigint - quickest way

> On Wed, Nov 16, 2016 at 7:49 AM, Merlin Moncure < mmonc...@gmail.com > wrote: >> On Fri, Nov 11, 2016 at 9:30 AM, Andreas Brandl < m...@andreas-brandl.de > >> wrote: >> You just posted the same question a few days ago -- were the answers >> there uns

[GENERAL] Partial update on an postgres upsert violates constraint

Hi, Basically I wanted to do a partial update inside pg (9.5), but it seems that a partial update fails when not all of constraint is fulfilled (such as the not null constraint) Below are the sql queries I used, CREATE TABLE jobs ( id integer PRIMARY KEY, employee_name TEXT NOT NULL,

Re: [GENERAL] Partial update on an postgres upsert violates constraint

Is there any way to check whether the row already exists before checking constraints ? I still want it to fail if it turns out to be a new row (which would violate the not null constraint), but updates the row if it already exists. Since if that is not possible, I would need to do a query to deter

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] 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] pg_restore to a port where nobody is listening?

ome/postgres/ [PG961] pg_restore -V pg_restore (PostgreSQL) 9.6.1 postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p 5439 -F d -C -j 2 /var/tmp/exp/ This runs fine but where does it connect to? Nothing is listening on port 5439. No, that can't run. Andreas

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

[GENERAL] Timestamp index not being hit

Hello Ive been running into an issue with postgresql not hitting index on select queries. Below is the sql query I used to test my issue. CREATE TABLE idxtbl ( id BIGINT, aint BIGINT, btime TIMESTAMPTZ, ctext TEXT, dbool BOOLEAN, PRIMARY KEY(id) ); --Inserted 10 mil random data /* TEST TIMESTAMP

Re: [GENERAL] Pgbasebackup help

> John R Pierce hat am 3. Dezember 2015 um 21:38 > geschrieben: > > > On 12/2/2015 9:59 PM, Yelai, Ramkumar IN BLR STS wrote: > > I can't shutdown the database during the backup and unable to use file > > system copy of data folder as it creates inconsistency > > If you invoke pg_start_back

Re: [GENERAL] BDR: ALTER statement hanging

commands all nodes MUST be active in replication, so have you checked that in pg_replication_slots? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would rec

[GENERAL] question about replication slots

Hi @ll, imagine a streaming replication using physical replication slots. And sometime a fail over. All okay. I take a basebackup and rebuild the old master as slave. Is there a risk that the new slave contains active replication slots but no listener on it? What have i to consider? Thx. -- S

Re: [GENERAL] BDR: ALTER statement hanging

for your problem, because of: "i- Joining: The node is doing initial slot creation or an initial dump and load". But i can't tell you why this nodes are in this state. Regards, Andreas -- 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] question about replication slots

Michael Paquier wrote: > On Sat, Dec 5, 2015 at 2:35 AM, Andreas Kretschmer > wrote: > > imagine a streaming replication using physical replication slots. And > > sometime a > > fail over. All okay. I take a basebackup and rebuild the old master as > > slave. &g

Re: [GENERAL] Interrupted streaming replication

the master knows nothing about this copy and can't stream its content. Maybe you can create/update your recovery.conf, see restore_command. for the future: increase wal_keep_segments, or better: use replication slots (since 9.4 a really good feature to prevent such errors). Andrea

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

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] 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? st

[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 Tor

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

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] Error promoting slave on cascading replication using replication slots

rocedure, afaik. Slave2 is now in sync with the new timeline, everything is okay. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile pe

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] Unique index problem

sce2_idx" DETAIL: Key ((COALESCE(a::text, 'NULL'::text)), (COALESCE(b::text, 'NULL'::text)), (COALESCE(c::text, 'NULL'::text)))=(1, 2, NULL) already exists. test=*# Maybe there are better solutions, it's a quick hack ;-) > > > DISCLAIMER:

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

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] Transfer db from one port to another

p.sql (untestet, please read *before* the documentation) I think, this should work. No idea what's wrong with pgAdmin, not using that. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linu

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

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 dest

Re: [GENERAL] grep -f keyword data query

n you show us the create table and create index - statements? And, the explain analyse of your query? And, finally, the pg-version? You got an index-scan, seems okay for me. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect.

Re: [GENERAL] grep -f keyword data query

ate table keywords ( id int not null primary key, name varchar(40) not null, url text not null ); create index ix_url_url_lists on url_lists(url); create index ix_url_keywords on keywords(url); create index ix_name_keywords on keywords(name); And he gave me a new explain, but not a explain ANA

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] Calling function (table_name, schema_name) within event trigger

Triggers on Events (40.9.2). If 9.5 contains more information than should someone fix the doku. Regards, Andreas -- 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] 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] 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] Calling function (table_name, schema_name) within event trigger

igger_for_create(); Demo: test=*# create table foo (i int); NOTICE: CREATE TABLE created object: public.foo CREATE TABLE test=*# Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) &q

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. A

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.

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'

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

) (2,5) (3,4) (3,5) (4,5) (10 rows) or test=*# select array[a.*, b.*] from unnest( array[1,2,3,4,5] ) a cross join unnest( array[1,2,3,4,5] ) b where a < b; array --- {1,2} {1,3} {1,4} {1,5} {2,3} {2,4} {2,5} {3,4} {3,5} {4,5} (10 rows) (matches the excpected result) Andr

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 r

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.

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 s

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

-is-taking-so-long Because of MVCC / visibility a seq-scan is requisite. For a guestimate about the number of rows you can look into pg_class. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus T

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] PostgreSQL upgrade 9.3.4 -> 9.3.10

re details here and also what steps needs to be > done to upgrade to 9.3.10 level? Just install the new version and restart PG. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds)

Re: [GENERAL] master slave failover - secondary slaves

gt; > Can anyone clear this up? Yeah, you have to change the recovery.conf to point to the new master. Read more here: http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-timeline-switch-of-slave-node-without-archives/ Andreas -- Really, I'm not out to des

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] Performance options for CPU bound multi-SUM query

see http://www.cybertec.at/postgresql_produkte/agg-parallele-aggregierungen-fuer-postgresql/ (and ask Hans for an english docu!) But, i see, it needs 9.5. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (L

Re: [GENERAL] Replication Question

to the new master, and, of course, the new master should stream the wals (wal_sender, proper pg_hba.conf and so on). Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I

Re: [GENERAL] Cannot start the PostgreSQL service

tem-user-permissions to start the service. pg_hba.conf is for client-access, not for startup! Maybe this here can help you: http://www.postgresql.org/message-id/f4c27e77f7a33e4ca98c19a9dc6722a2062ad...@exchange.corp.perceptron.com and http://www.postgresql.org/message-id/4c2940fc.6000...@enterprise

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] 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] CONCAT returns null

t; Why? I tried to add ' with '::VARCHAR and ' with '::TEXT but the result is > still NULL. > > Thank you NULL concat with a value returns NULL. You can avoid that using COALESCE(value, ''), that returns the value, or, if the value NULL, ''. Andreas -

Re: [GENERAL] CONCAT returns null

-string.html > " > concat(str "any" [, str "any" [, ...] ]) textConcatenate the text > representations of all the arguments. NULL arguments are ignored. > " you are right, my fault :-(, muddled with the || - operator. Andreas -- Really, I

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] "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] 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] Insert data in two columns same table

> > > Hi Andreas! > > Well... > > There are two tables that I need to get data from(dm.billables / > public.ja_mobiusers), and a third table (dm.billables_links) that I need to > insert data from those two tables. lets start from here. you have 2 tables: test=*

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

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

<    1   2   3   4   5   6   7   8   9   10   >