Re: [GENERAL] 9.4+ partial log-shipping possible?

Sven Geggus wrote: > So here is my question: > > Would it be possible to have a setup, where one master data database will > act in the above matter (still running osm2pgsl) but will also provide > publicly availabe data for log-shipping standby servers? > > We would need to be able to explicit

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

Vincent Veyron wrote: > On Fri, 1 May 2015 14:12:08 + > Alanoly Andrews wrote: > > > Hello, > > > > We have a database that has been unloaded using pg_dump. This database has > > a table with a field defined as "lo". When restoring this database to > > another system, we want to avoid lo

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

ben.play wrote: > Hi all, > > We have a large database with postgre 9.3 (>300 Gb) and our queries are > (really) long for 6 days without changing anything. > > After seeing our log, I saw that the same query on an old data was quick but > the same query with new data are really slow. please

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

ben.play wrote: > Thank you for your quick answer ! > > And ... you are a genius :) Yeah, i know ;-) > > A simple "analyse > " resolved my problem. > Do We have to do it regularly ? it's running regulary (via vacuum-process), but you can (and should) run it after mass data changes. An

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 recompile penguin with --en

Re: [GENERAL] replacing jsonb field value

Michael Paquier wrote: > > Append the new value to it the existing field, jsonb has as property > to enforce key uniqueness, and uses the last value scanned for a given > key. can you show a simple example, how to append a jsonb to an jsonb-field? Maybe i'm blind, but i can't find how it works.

Re: [GENERAL] replacing jsonb field value

Glyn Astill wrote: > Prior to 9.5 you can't, I think you have to use something like jsonb_each to > unwrap it then wrap it back up again. > > The jsonbx extension, which I believe is what ended up in 9.5 has a simple > concatenate function (here: https://github.com/erthalion/jsonbx), I also ha

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

Seann Reed wrote: > Everything worked fine with the old setup.  With the new setup, I can access > my > database through psql command-line.  However, I have a compiled program called > 'Shefdecode' that makes queries to the database through ECPG and this program > cannot access the database.  I

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 unintentional side effect.

[GENERAL] [9.5] question about row level security

Hello @ll, how can i define a policy to prevent update a single field but enable update other fields in a row? For instance, a staff member table. the boss should be able to change all fields but not a specific field in all rows, but should be able to update this field for his own row. id | name

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

hi @ll, my table: test=*# \d rls_test Table "public.rls_test" Column | Type | Modifiers +-+--- id | integer | name | text| txt| text| Indexes: "idx_name" btree (name) Policies: POLICY "my_policy" FOR ALL USING (name = ("current_u

Re: [GENERAL] Rounding Float Array

Alex Magnum wrote: > Hello, > > I have a float array holding geo location information. > >               geoloc > --- >  {5.3443133704554,100.29457569122} >  {5.3885574294704,100.29601335526} >  {3.1654978750403,101.60915851593} >  {5.3766154817748,100.3147244453

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! Andreas -- Really, I'm not out to

Re: [GENERAL] postgres standby won't start

Ramalingam, Sankarakumar wrote: > We have a standby set up between two sites in two different locations. The > replication was going on well and suddenly it stopped due to error > > 2015-09-08 16:07:51 EDT LOG: streaming replication successfully connected to > primary > > 2015-09-08 16:07:51 E

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

hello @ll, i have a simpe table : test=# create table httpd_log(id serial primary key, data text); CREATE TABLE and i'm trying to import from a httpd-log, that contains this: kretschmer@tux:~$ cat test.log ... domain.de aaa.63.xx.yy - - [06/Nov/2014:00:48:22 +0100] "GET /index.php/impressum2/

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

Dane Foster wrote: > Hello, > > I was wondering when returning JSON data from a PostgreSQL function for > consumption by clients (e.g., PHP or Lua) does it make any difference to > declare the function's return type as JSON or JSONB? JSONB is only for internal storage, for external representati

Re: [GENERAL] temporary indexes?

Jonathan Vanasco wrote: > I couldn't find any mention of this on the archives... > > Have the project maintainers ever considered extending CREATE INDEX to > support "temporary" indexes like CREATE TEMPORARY TABLE? Not sure if you mean something like this: http://www.depesz.com/2015/09/07/hypo

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: 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] 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

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

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

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

Selim Tuvi wrote: > Hi, I am running a three node BDR cluster. BDR version is 0.9.3. Postgres > version is 9.4.5. > > With 0.9.2, I used to be able to issue ALTER statements using psql and it > would > go through. This time it is just hanging. The statement is this: for ddl-commands all nodes

[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

> Selim Tuvi hat am 4. Dezember 2015 um 18:46 geschrieben: > > > Yes they seem to be active: > > deliver=# select * from pg_replication_slots; > slot_name | plugin | slot_type | datoid | > database | active | xmin | catalog_xmin | restart_lsn > ---

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

Tony Nelson wrote: > 2015-12-07 08:24:50 EST FATAL: requested WAL segment 00010089002C > has already been removed > > > > Can I simply copy the file from my archive directory back to the WAL > directory? I'm afraid that won't work, because of the master knows nothing about this

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

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? start psql with -E Andreas -- R

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

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

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

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

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

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] 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] 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] 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] 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 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] 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 sure) Andreas --

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

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

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

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

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

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] 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] 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] How to install Postgresql on MS Vista?

dfx <[EMAIL PROTECTED]> schrieb: > I tryied it but get errors on create user postgres. > > Is there some workaround? I'm not familiar with this crappy OS, but maybe you should disable UAC. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side e

Re: [GENERAL] How to install Postgresql on MS Vista?

Joshua D. Drake <[EMAIL PROTECTED]> schrieb: > >>I tryied it but get errors on create user postgres. > >>Is there some workaround? > >I'm not familiar with this crappy OS, but maybe you should disable UAC. > > In your mind, it may be crappy but it is indeed an officially supported > operating sy

Re: [GENERAL] SQL problem..

Bauhaus <[EMAIL PROTECTED]> schrieb: > Hello, > > I'm an Access/SQL novice and I have an sql problem: > > I have the following table Price: > > FuelID PriceDate Price > LPG1/05/2007 0,2 > LPG13/05/2007 0,21 > SPS 2/05/2007 1,1 > SPS 15/05/2007 1,08 > > And I have to make

Re: [GENERAL] serial grows on failed requests

rihad <[EMAIL PROTECTED]> schrieb: > When I do an insert that fails (like FK inconsistency, illegal value, etc.) > the users.id grows nonetheless... This is unacceptable for my current normal behavior. > needs. Any way to prevent that while still maintaining ease of use? Using > PostgreSQL 8.

Re: [GENERAL] creating/dropping tables inside functions?

George Pavlov <[EMAIL PROTECTED]> schrieb: > foo=> select * from f(); > ERROR: relation with OID 1469396 does not exist > CONTEXT: SQL statement "SELECT a from t" > PL/pgSQL function "f" line 4 at select into variables > > the second invocation does not see the newly created temp table... Righ

Re: [GENERAL] Request: Anyone using bogus / "humorous" X-Message-Flag headers, could we please turn them off

Bill Bartlett <[EMAIL PROTECTED]> schrieb: > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of A. Kretschmer > > Sent: Friday, October 05, 2007 10:57 AM > > To: pgsql-general@postgresql.org > > Subject: Re: [GENERAL] Request: Anyone using bo

Re: [GENERAL] drop table cascade doesn't drop manual sequences

Tom Lane <[EMAIL PROTECTED]> schrieb: > "Scott Marlowe" <[EMAIL PROTECTED]> writes: > > On 10/10/07, Guilherme <[EMAIL PROTECTED]> wrote: > >> If I insert a sequence later on table creation with alter table, drop > >> table cascade simply doesn't drop this sequence even when I specify > >> CASCADE

Re: [GENERAL] pg_dump SERIAL and SEQUENCE

Laurent ROCHE <[EMAIL PROTECTED]> schrieb: > Then when I drop tab_b, the SEQUENCE tab_b_colb1_seq is not dropped, however > when I drop tab_a, the SEQUENCE tab_a_cola1_seq is dropped too ! > I am using PG 8.1 and ALTER SEQUENCE ... OWNED BY does not exist ! ! ! Read this message: <[EMAIL PROTE

Re: [GENERAL] INDEX and JOINs

Reg Me Please <[EMAIL PROTECTED]> schrieb: > How can I "Increasing the statistics target for the larger table"? > I'ìm sorry for asking, but I'm not that deep into RDBMS. alter table alter column SET STATISTICS ; Andreas -- Really, I'm not out to destroy Microsoft. That will just be a complet

  1   2   3   4   5   >