Re: [GENERAL] pg_restore load data

On Thu, Nov 16, 2017 at 1:07 PM, Ron Johnson wrote: > v9.2.7 (Yes, I know, it's old. Nothing I can do about it.) > > During a "whole database" restore using pg_restore of a custom dump, when > is the data actually loaded? I've looked in the list output and don't see > any "load" statements. >

Re: [GENERAL] Questionaire: Common WAL write rates on busy servers.

On Mon, Apr 24, 2017 at 9:17 PM, Andres Freund wrote: > > Questions (answer as many you can comfortably answer): > - How many MB/s, segments/s do you see on busier servers? > - What generates the bulk of WAL on your servers (9.5+ can use > pg_xlogdump --stats to compute that)? > - Are you seein

Re: [GENERAL] Re-sync slave server

On Mon, Jun 6, 2016 at 5:32 PM, Patrick B wrote: > My set up: > > > Master --> slave01 (streaming replication) --> slave02 (streaming > replication) > Master --> slave03 (wal_files 4 days old, not streaming replication) > > The wal_files are stored into each server. > But when the slave03 was dow

Re: [GENERAL] More correlated (?) index woes

On Tue, Mar 29, 2016 at 3:47 AM, Geoff Winkless wrote: > On 28 March 2016 at 20:23, I wrote: > >> Table pa has 7522676 rows, 4834042 of which have field1 NULL, so it's >> absolutely not reasonable to expect this to be an optimal strategy. >> ​ >> > It occurred to me that even though the majority

Re: [GENERAL] vacuum - reclaiming disk space.

On Thu, Mar 17, 2016 at 7:27 AM, Mike Blackwell wrote: > I have a large table with numerous indexes which has approximately doubled > in size after adding a column - every row was rewritten and 50% of the > tuples are dead. I'd like to reclaim this space, but VACUUM FULL cannot > seem to finish

Re: [GENERAL] Subtract one array from another, both with non-unique elements

On Sun, Mar 6, 2016 at 9:22 AM, Alexander Farber wrote: > could someone please recommend the most efficient way subtracting elements > of one array from the other in PostgreSQL 9.5? > There are quite a few examples of array functions at http://postgres.cz/wiki/Array_based_functions, probably wor

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 2:35 PM, drum.lu...@gmail.com wrote: > rsync -azr --progress --partial postgres@$MASTER_IP: >> /var/lib/postgresql/data/var/lib/postgresql/data/ --exclude >> postmaster.pid > > > Ah ok! So this will do an incrementa, right? not supposed to copy ALL the > base/ again? > Yes

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 2:22 PM, drum.lu...@gmail.com wrote: > *rsync would be something like:* > > from slave1: > rsync -av data/pg_xlog slave2:/var/lib/postgresql/data/ > Normally I run something like the following from the slave I am setting up. rsync -azr --progress --partial postgres@$MASTE

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 2:10 PM, drum.lu...@gmail.com wrote: > I could stop the slave then But I'm afraid getting it back online and > get some other errors > At this point I think your options are slim. If you are feeling adventurous, you can try doing the rsync with the slave running, then

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 1:54 PM, drum.lu...@gmail.com wrote: > Hi, > > If you are able to stop Postgres on the slave you are taking the base >> backup from, you could do this: > > > I'm not... the data base is 2 TB. > So, a RSYNC would take DAYS. And I'm not able to stop the SLAVE for > that

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 1:49 PM, drum.lu...@gmail.com wrote: > Hi, > > If the master is successfully ships WALs to the slave you are setting up >> you do not need the "stream" option. > > > yes.. the master is successfully shipping the WALs > > Is there anything else? Help, please hehehehe >

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 12:36 PM, drum.lu...@gmail.com wrote: > Do you have any other ideia? Do you know if --xlog it's the problem and I > should re-run the pg_basebackup again with the *--xlog-method=stream* > option? > If the master is successfully ships WALs to the slave you are setting up y

Re: [GENERAL] New Slave - timeline ERROR

On Fri, Jan 8, 2016 at 8:44 PM, drum.lu...@gmail.com wrote: Hi, I'm a bit too lazy to try suss out the exact reasons for your failure, but here is a reasonably thorough guide to set up replication: http://dba.stackexchange.com/a/53546/24393 A few tips: - Having the master ship WALs to the slaves

Re: [GENERAL] Submitting to this list

On Wed, Jan 6, 2016 at 9:15 AM, Melvin Davidson wrote: > > I would like to see the following added to the introduction to this list. > > "When submitting to this list, please include the full version of > PostgreSQL and the O/S you are using. > Also, if you are reportaing a problem, it is essenti

Re: [GENERAL] PostgreSQL GUI tools

On Sun, Jan 3, 2016 at 2:15 AM, subhan alimy wrote: > Is there any PostgreSQL GUI tools to pull the tables structure along with > the relationships? > Along with the other suggestions, I have personally used http://www.dbschema.com/. It works well, but I don't recall if there was a free version.

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

On Thu, Oct 22, 2015 at 9:15 AM, Tom Lane wrote: > bricklen writes: > > What would have happened to the WAL-shipping-only standby if the WALs > were > > all applied? Would it have it balked at applying a WAL containing bad > data > > from the master, or would

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

A follow-up question if I may, > bricklen writes: >> > Yes, it is definitely a table. There was originally an index on that >> table >> > which threw the original error (about sibling mismatch). I dropped the >> > index and attempted to recreate it, which

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

On Wed, Oct 21, 2015 at 12:52 PM, Tom Lane wrote: > bricklen writes: > > Yes, it is definitely a table. There was originally an index on that > table > > which threw the original error (about sibling mismatch). I dropped the > > index and attempted to recreate i

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

Hi Tom, On Wed, Oct 21, 2015 at 11:46 AM, Tom Lane wrote: > bricklen writes: > > I get the following output for ctid, id, other_id, tstamp: > > > (690651,42) |318698967 | 347978007 | 2015-10-20 01:55:41.757+00 > > (690651,43) |318698968 | 347978008 | 20

[GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

mpting zero_damaged_pages cause any harm as the first step (other than the obvious destruction of any bad pages)? Is this the correct command if option #2 is chosen? Can it be executed against a running cluster? dd if=/dev/zero of=database/16750/27244 bs=8192 seek=690652 count=1 conv=notrunc Thanks, Bricklen

[GENERAL] From: Bricklen Anderson

Hi http://forum.myways.su/felt.php?drive=bhankyuytv3630es brick...@gmail.com

Re: [GENERAL] Backups over slave instead master?

On Thu, May 15, 2014 at 1:55 PM, Bruce Momjian wrote: > On Thu, May 1, 2014 at 12:39:44PM -0700, bricklen wrote: > > Or alternatively, if "backup" = pg_dump, then backups can taken from the > slave > > too. Have a look at pg_xlog_replay_pause() + pg_dump + > pg

Re: [GENERAL] copy expensive local view to an RDS instance

On Tue, May 6, 2014 at 8:07 AM, Marcus Engene wrote: > On 06/05/14 16:58, bricklen wrote: > >> >> A very quick search shows that rds supports dblink, so perhaps that would >> work. >> http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/ >> CHAP_PostgreSQL.h

Re: [GENERAL] copy expensive local view to an RDS instance

On Tue, May 6, 2014 at 5:52 AM, Marcus Engene wrote: > Hi, > > I have a local db behind a firewall etc. Basically, I'd like to do what > I'd locally would... > > create table abc > as > select > * > from > local_expensive_view; > > abc - on RDS > local_expensive_view - on local machine >

Re: [GENERAL] Backups over slave instead master?

On Thu, May 1, 2014 at 8:54 AM, Shaun Thomas wrote: > On 05/01/2014 10:31 AM, Edson Richter wrote: > > I'm wondering if would be possible to execute these backups in the slave >> server instead, so I can avoid the overhead of backups on master system? >> > > If you're on PostgreSQL 9.3, you can b

Re: [GENERAL] importing a messy text file

On Wed, Apr 30, 2014 at 1:07 AM, Willy-Bas Loos wrote: > > Hi, > > I have a 56GB textfile that i want to import into postgres. > The file is tab delimited and not quoted. > Would Pgloader be an option? http://tapoueh.org/pgloader/

Re: [GENERAL] Log file monitoring and event notification

On Sat, Apr 5, 2014 at 8:47 AM, Andy Colson wrote: > Hi All. > > I've started using replication, and I'd like to monitor my logs for any > errors or problems. I don't want to do it manually, and I'm not interested > in stats (a la PgBadger). > > What I'd like, is the instant PG logs: "FATAL: wal

Re: [GENERAL] Playing with 9.4devel - unnest

On Mon, Mar 10, 2014 at 10:16 AM, Tim Kane wrote: > But what I really wanted to do, is unnest multiple sets of array values as > returned from a table/query.. > Craig Ringer posted an interesting answer to a somewhat related question a few months ago on Stack Overflow: http://stackoverflow.com/a

Re: [GENERAL] high throughput 9.3, master/slave question

On Fri, Feb 28, 2014 at 1:54 PM, Matthew Chambers wrote: > > Initially, I had my application servers using the slave for short, read > only queries, but this turned out to be highly unstable. The slave would > start refusing connections, and the logs would fill with: > > ERROR: canceling statemen

Re: [GENERAL] pg_dump/pg_restore issues

On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé wrote: > Hi, I'm backing up a big database using the --exclude-table option for > two tables, say table1 and table2. Then another backup of only those > tables, so, the final result are three backup files. > > basic.backup > table1.backup > table2.

Re: [GENERAL] Streaming Replication - Error on Standby

On Tue, Feb 11, 2014 at 11:25 AM, bobJobS wrote: > > To get the standby server to a point, I tool a globals dump and a data dump > of the primary server and build the standby. > > Then I executed pg_startbackup, rsync data dir to standby data dir (to > catch > any changes made while I was buildin

Re: [GENERAL] Streaming Replication - Error on Standby

On Tue, Feb 11, 2014 at 10:12 AM, bobJobS wrote: > Postgres 9.3.2. > RHEL 5 > > After performing all of the Streaming Replication setup steps, > What replication steps? > database system identifier differ between the primary and standby > How did you take the initial backup of the master? D

Re: [GENERAL] Better Connection Statistics

On Fri, Feb 7, 2014 at 2:24 PM, bricklen wrote: > > On Fri, Feb 7, 2014 at 10:08 AM, Shaun Thomas wrote: > >> s I said in the original message, pg_stat_statements only gives query >> stats for the whole database. What I want to know, is information about >> each cli

Re: [GENERAL] Better Connection Statistics

On Fri, Feb 7, 2014 at 10:08 AM, Shaun Thomas wrote: > > > Perhaps this might be of use. > > > > http://www.postgresql.org/docs/current/static/pgstatstatements.html > > Nope. As I said in the original message, pg_stat_statements only gives > query stats for the whole database. What I want to know,

Re: [GENERAL] Grep'ing for a string in all functions in a schema?

On Thu, Jan 30, 2014 at 12:45 PM, Wells Oliver wrote: > Since Postgres does not consider a table as a dependency of a function if > that table is referenced in the function (probably a good reason), I often > find myself in a position of asking "is this table/sequence/index > referenced in any of

Re: [GENERAL] Filtering queries by IP

On Mon, Jan 20, 2014 at 6:20 PM, Sergey Konoplev wrote: > On Mon, Jan 20, 2014 at 5:21 AM, Leonardo M. Ramé > wrote: > > Hi, I'm trying to find the cause of slow performance on some screens of > > an application. To do that, I would like to be able to log all the > > queries made by an specific

Re: [GENERAL] Any freeware graphic display of DDL software available?

On Thu, Jan 16, 2014 at 2:57 PM, Susan Cassidy < susan.cass...@decisionsciencescorp.com> wrote: > It doesn't appear that DBVisualizer does an ER type diagram, which is what > I really need. > > DBSchema outputs ER diagrams: http://www.dbschema.com/database-er-diagrams.html I think the paid versio

Re: [GENERAL] How to delete completely duplicate rows

On Wed, Jan 1, 2014 at 4:14 AM, Janek Sendrowski wrote: > I want to delete duplicates in my table. I've dropped the unique > constraint to insert my data. > My id value is a hash calculated witch the values of the two other columns. > So I want to delete all columns, which are indentical, but kee

Re: [GENERAL] How to list current user and database in PSQL

On Tue, Dec 31, 2013 at 10:55 AM, peterlen wrote: > After logging into the PSQL Shell Script as a particular user and > database, I > want to display who my current user is and what database I am in as I may > have forgotten who I am logged in as in order to make sure any commands are > going to

Re: [GENERAL] unexpected pageaddr error in db log

On Thu, Dec 19, 2013 at 1:27 AM, wd wrote: > > On Thu, Dec 19, 2013 at 2:24 PM, Amit Langote wrote: > >> "unexpected pageaddr" log entry in this case means the standby reached >> the end of existing WAL. >> So, just before connecting to walsender for streaming replication, it >> logs this. >> > >

Re: [GENERAL] Zero dead tuples, when significant apparent bloat

On Tue, Dec 10, 2013 at 5:05 PM, John Melesky wrote: > It seems clear that there were dead tuples, since the table size shrank to > an eighth of its previous size. Why did analyze not pick that up? > Am I missing something? > This is a very large database, so we want to introspect against live/dea

Re: [GENERAL] DB Audit

On Tue, Dec 10, 2013 at 6:53 AM, wrote: > I am using sybase ase as dbms and I would migrate to postgresql, but > the absence of a built in DB audit functionality is a show stopper for me. > > So I would know if there is a way to get information about DB events like: > > server boots > > login &

Re: [GENERAL] Mismatched pg_class.reltuples between table and primary key

Hi Alvaro, Thanks for the link. On Mon, Dec 2, 2013 at 4:35 PM, Alvaro Herrera wrote: > bricklen escribió: > > We recently experienced a hard crash of a dev server due to lack of > > resources (we think, still investigating). > > That resulted in an interesting scenario wh

[GENERAL] Mismatched pg_class.reltuples between table and primary key

We recently experienced a hard crash of a dev server due to lack of resources (we think, still investigating). That resulted in an interesting scenario where one of the tables was returning 12 rows from "select * from tbl", but "select * from tbl order by " was only returning 11. Looking at pg_clas

Re: [GENERAL] unnest on multi-dimensional arrays

the liberty of adding your nifty function to the Postgresql Wiki at https://wiki.postgresql.org/wiki/Unnest_multidimensional_array Feel free to edit directly or suggest any changes to it. Cheers, Bricklen

Re: [GENERAL] nested query vs left join: query planner very confused

On Wed, Nov 27, 2013 at 7:56 AM, David Rysdam wrote: > > > At my client's location, the query is very slow (same table size, > similar hardware/config, although they are running 9.0.x and I'm on > 9.2.x). By "slow" I mean like an *hour*. 'explain' has this structure: > > Seq scan on mags >

Re: [GENERAL] Postgres as In-Memory Database?

On Mon, Nov 18, 2013 at 11:16 PM, Stefan Keller wrote: > > > I don't think there's any evidence that the Postgres developers ignore > > useful optimisations. What you're arguing is that the optimisation > > you have in mind isn't covered. > > No; my point is that I - and others like Stonebraker,

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

On Mon, Nov 18, 2013 at 8:30 PM, Brian Wong wrote: > I've tried any work_mem value from 1gb all the way up to 40gb, with no > effect on the error. I'd like to think of this problem as a server process > memory (not the server's buffers) or client process memory issue, primarily > because when w

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

On Mon, Nov 18, 2013 at 12:40 PM, Brian Wong wrote: > We'd like to seek out your expertise on postgresql regarding this error > that we're getting in an analytical database. > > Some specs: > proc: Intel Xeon X5650 @ 2.67Ghz dual procs 6-core, hyperthreading on. > memory: 48GB > OS: Oracle Enterp

Re: [GENERAL] pg_log filling up with false logs

On Mon, Nov 11, 2013 at 6:32 PM, Shree wrote: > Also pg_ctl command to stop, restart does not seem to have any effect. > Does anyone have any ideas? > I can't speak to what issued the shutdown command, but have you tried "pg_ctl restart -m fast" to expedite the restart (if that is indeed what y

Re: [GENERAL] Full text search on partial URLs

On Wed, Nov 6, 2013 at 10:53 AM, Zev Benjamin wrote: > On 11/06/2013 01:47 PM, bricklen wrote: > >> >> On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin >> mailto:zev-pg...@strangersgate.com>> wrote: >> >> Hi, >> >> I have Postgr

Re: [GENERAL] Full text search on partial URLs

On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin wrote: > Hi, > > I have Postgres full text search set up for my application and it's been > working great! However, my users would like their searches to turn up > parts of URLs. For example, they would like a search for "foobar" to turn > up a docum

Re: [GENERAL] autovaccum task got cancelled

On Thu, Oct 31, 2013 at 11:51 AM, Gary Fu wrote: > Hello, > > I'm running an application (with programs in Perl) through pgpool 3.1 with > replication mode to two postgresql db servers (version 9.0.13). Recently, > I noticed that the following messages repeatedly showed in postgres log > files.

Re: [GENERAL] Need some help on Performance 9.0.4

On Sun, Oct 13, 2013 at 8:43 AM, akp geek wrote: > Sorry all not posting clear. I posted our postresql.conf file. What we > are having is very slow response from the database. > > would like to get some feedback about postgresql.conf file parameters that > I posted are causing the issue for perf

[GENERAL] Re: [GENERAL] Forms for entering data into postgresql‏

On Wed, Oct 9, 2013 at 7:05 PM, Sudhir P.B. wrote: > I have developed an application using MS SQL. I have used MS Access for > creating forms to enter data into the database. I am thinking of changing > over to postgresql and would also like to use any other available open > source tool for creat

Re: [HACKERS] [GENERAL] Urgent Help Required

On Tue, Oct 8, 2013 at 8:25 AM, shailesh singh wrote: > yes i am executing psql to connect to this database. > > su - postgres > psql patnadbold > ..and then what? Does it immediately throw an error stating that you must issue a VACUUM?

Re: [GENERAL] Many thousands of partitions

On Tue, Oct 8, 2013 at 8:23 AM, Grzegorz Tańczyk wrote: > Hello, > > I have question regarding one of caveats from docs: > http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html > > If you are using Postgresql 8.3 then you should consider upgrading to 9.3 instead. > "Partitioning using

Re: [HACKERS] [GENERAL] Urgent Help Required

On Tue, Oct 8, 2013 at 8:13 AM, shailesh singh wrote: > > On Tue, Oct 8, 2013 at 8:36 PM, bricklen wrote: > >> >> On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh wrote: >> >>> HINT: To avoid a database shutdown, execute a full-database VACUUM in >>&

Re: [HACKERS] [GENERAL] Urgent Help Required

On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh wrote: > HINT: To avoid a database shutdown, execute a full-database VACUUM in > "patnadbold". > ERROR: could not access status of transaction 33011 > DETAIL: could not open file "pg_clog/": No such file or directory > exit > > > After this i am a

Re: [GENERAL] Fighting the autovacuumer (to prevent wraparound)

On Thu, Oct 3, 2013 at 11:48 AM, Michael Graham wrote: > Hi all, > > We partition the data in postgres in a per-month basis and run a script > to delete old partitions. Does "delete" = "drop"? > Sometimes this script fails and the delete > doesn't happen because of a deadlock, today I notice

Re: [GENERAL] how to know cpu utilization using sql command

On Thu, Oct 3, 2013 at 1:50 AM, M Tarkeshwar Rao < m.tarkeshwar@ericsson.com> wrote: > How to know cpu and memory utilization in postgres. > > We require this information to show it on our Application using any > postgres command or tool. > pg_activity works pretty well: https://github.

Re: [GENERAL] partitioning for speed, but query planner ignores

On Wed, Oct 2, 2013 at 9:01 AM, David Rysdam wrote: > I had some vague notion of tables "doing work" but really if it can load > one > partition into RAM and get most of my hits from there, it'd be a big > win. The same concept applies to the frequently-used indexes on that partition.

Re: [GENERAL] How do I find a trigger function that is raising notices?

On Thu, Sep 26, 2013 at 9:04 AM, Rob Richardson wrote: > Select * from pg_proc where lower(prosrc) = '%%' A slight revision should work: select distinct proname from pg_proc where prosrc ilike '%%';

Re: [GENERAL] how to tell master from replica

On Wed, Sep 25, 2013 at 5:11 PM, bricklen wrote: > > On Wed, Sep 25, 2013 at 4:53 PM, Scott Ribe > wrote: > >> Assuming a master & replica set up using streaming replication, hot >> standby, 9.3. >> >> I'd like to have a single script on both machin

Re: [GENERAL] how to tell master from replica

On Wed, Sep 25, 2013 at 4:53 PM, Scott Ribe wrote: > Assuming a master & replica set up using streaming replication, hot > standby, 9.3. > > I'd like to have a single script on both machines distinguish whether it's > running on the machine with the master or replica, and take different > actions

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 9:50 AM, Andrus wrote: > http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server > wrote that max reasonable value in Windows for shared_buffers is 512MB > Is my setting shared_buffers= 2400MB reasonable in Windows ? > Someone else will hopefully answer that questi

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 9:12 AM, Andrus wrote: > > work_mem = 400MB > > Hope that this is OK. > For cluster-wide setting you will probably want to drop that significantly -- start lower, as in somewhere around 10MB and work up from there as necessary. For the queries you are finding slow (the re

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 8:33 AM, Andrus wrote: > Hi! > >Could you also post the results of the following query? > >SELECT name, current_setting(name), source > >FROM pg_settings > >WHERE source NOT IN ('default', 'override'); > In real server where problem is: > > > 21 shared_buffers

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 8:06 AM, Andrus wrote: > > >Could you please post EXPLAIN for that query? > Could you also post the results of the following query? SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override'); Also, what is the total memory in

Re: [GENERAL] Why does this array query fail?

On Mon, Sep 16, 2013 at 6:36 PM, Ken Tanzer wrote: > Thanks for the explanation. I think I at least understand what it's doing > now. I'm either surprised or confused though, as I was under the > impression that you could substitute a subquery for a value pretty much > anywhere, but I guess tha

Re: [GENERAL] Why does this array query fail?

On Mon, Sep 16, 2013 at 5:32 PM, Ken Tanzer wrote: > SELECT 'found' WHERE 'test' = ANY( (SELECT ARRAY['test','pass','fail']) ); > It works if you drop the inner SELECT. SELECT 'found' WHERE 'test' = ANY( ARRAY['test','pass','fail'] );

Re: [GENERAL] fsync and wal_sync_method

On Wed, Sep 11, 2013 at 6:11 PM, ascot.m...@gmail.com wrote: > Hi, > > I am using pg 9.2.4 and ubuntu 12.04 to set up a pair of pg-Master and > pg-Slave with streaming replication. > > The archive_command is enabled and the "rsync" is used in pg-Master to > save all its archived WAL files to the 3

Re: [GENERAL] OLAP

On Tue, Aug 27, 2013 at 3:04 PM, Jerry Sievers wrote: > Vertica is another case of an analytics focused platform that dirived > from Postgres, version 8.0 IIRC. > FWIW, You might be thinking of Greenplum, and I think it forked from pg 8.1. Vertica was built from scratch, it is not using Postgres

Re: [GENERAL] Update quey

On Fri, Aug 23, 2013 at 8:04 AM, Hall, Samuel L (Sam) < sam.h...@alcatel-lucent.com> wrote: > I have a table (pubacc_lo) from the US government with 500,00+ rows. It > has latitude and longitude in three columns each for degrees, minutes and > seconds. I need a Point geometry column. So I wrote th

Re: [GENERAL] Column names for INSERT with query

On Thu, Aug 22, 2013 at 3:30 PM, Robert James wrote: > I would like to: > > INSERT INTO t > SELECT * FROM r, (x + y) AS field3 > INSERT INTO t (col1, col2, col3) SELECT ...

Re: [GENERAL] Temp files on Commit

On Thu, Aug 22, 2013 at 8:40 AM, Tom Lane wrote: > > There is some setting that controls whether such messages appear at > all > Is it "log_temp_files"?

Re: [GENERAL] pg_get_triggerdef can't find the trigger using OID.

On Fri, Aug 16, 2013 at 8:01 AM, AI Rumman wrote: > Why can't pg_get_triggerdef find the trigger using OID. > > testdb=# SELECT > testdb-# p.oid, > testdb-# n.nspname as "Schema", > testdb-# p.proname as "Name", > testdb-# pg_catalog.pg_get_function_result(p.oid) as "Result data type", >

Re: [GENERAL] devide and summarize sql result (all)

On Thu, Aug 15, 2013 at 1:51 PM, Janek Sendrowski wrote: > Hi, > > My sql query results sth. like this: > > user percentage > franz 78% > smith 98% > franz 81% > jason 79% > smith 89% > smith 85% > smith 99% > > Now I'd like to summarize the percentages oder every user like this. > smith > 2 matc

Re: [GENERAL] bi-directional syncing help request

On Fri, Aug 9, 2013 at 8:12 AM, Bèrto ëd Sèra wrote: > or you can just take a full dump from one box and import it on the other > any time you switch. If it's not a big db it should probably be quicker > than any alternative approach. > A pg_dump from one system or the other will effectively over

Re: [GENERAL] bi-directional syncing help request

On Fri, Aug 9, 2013 at 7:36 AM, Bèrto ëd Sèra wrote: > --all you need to do is switch master and slave so that "master" is the > one box you are currently on > That probably isn't a reasonable solution, considering the OP mentioned that she was not a professional DBA. Setting up a hot/warm standb

Re: [GENERAL] setting high value for wal_keep_segments

On Thu, Aug 8, 2013 at 6:23 AM, AI Rumman wrote: > Hi, > > I am going to sync slave with my master which is almost 500 G. I am not > using archive directory instead of I am using wal files for streaming. As > it may take almost 3 hours, I am thinking of setting up 400 for > wal_keep_segments wher

Re: [GENERAL] Alter table never finishes

On Tue, Jul 30, 2013 at 10:34 AM, bricklen wrote: > Sorry bricklen, I've killed all idle connections with "kill -9 ", > >> then I was able to execute the alter table. >> > > No problem. > btw, I meant 'no need to apologize that the queries were gone', not that I recommend "kill -9" (I don't!)

Re: [GENERAL] Alter table never finishes

ct pg_terminate_backend(pid) from pg_stat_activity where pid <> pg_backend_pid(); That is much safer than kill -9, which if issued against the parent postgres process will crash your cluster. > > Sorry bricklen, I've killed all idle connections with "kill -9 ", > then I was able to execute the alter table. > No problem.

Re: [GENERAL] Alter table never finishes

On Tue, Jul 30, 2013 at 10:07 AM, Leonardo M. Ramé wrote: > > select pg_cancel_backend(pid) from pg_stat_activity where pid <> > pg_backend_pid(); > > And it returned this: > > pg_cancel_backend > --- > t > t > (2 rows) > > But when I execute my update table command, it still nev

Re: [GENERAL] Fwd: corrupted files

On Tue, Jul 30, 2013 at 8:18 AM, Klaus Ita wrote: > > thank you! It turned out that really there was a corruption in the main pg > server which was 'virally' propagated to > > 1. streaming replica > 1. replaying wal receiver > 1. old backup that tried to replay the wal's > > I really thought with

Re: [GENERAL] Alter table never finishes

On Tue, Jul 30, 2013 at 7:50 AM, Leonardo M. Ramé wrote: > Hi, I need to do an alter table on a small table (~300 records), but it > never ends. It may be because there are clients using that table. > > How can I force disconnect all clients to let me alter that table?. > If you are using Postgre

Re: [GENERAL] Fwd: corrupted files

On Mon, Jul 29, 2013 at 11:50 PM, Klaus Ita wrote: > I am trying to remember, there was a tool that plotted the contents of the > wal_files in a more readable format ... > xlogdump? https://github.com/snaga/xlogdump

Re: [GENERAL] 5 is not a smallint but '5' is

On Mon, Jul 29, 2013 at 8:11 AM, John DeSoi wrote: > I was surprised to discover this today. I can work around it, but it seems > counterintuitive. If 5 can't be parsed as a smallint, I would feel better > if '5' was not one either. > > > temp=# create or replace function itest (param smallint) r

Re: [GENERAL] Rule Question

On Thu, Jul 25, 2013 at 4:18 AM, Giuseppe Broccolo < giuseppe.brocc...@2ndquadrant.it> wrote: > (TG_OP = 'UPDATE' AND > (NEW.b != OLD.b OR > (NEW.b IS NULL AND OLD.b IS NOT NULL) OR > (NEW.b IS NOT NULL AND OLD.b IS NULL) >

Re: Reply: [GENERAL] Can we specify transaction level

On Fri, Jul 19, 2013 at 5:08 PM, guxiaobo1982 wrote: > > what does a wrapper function mean, count is a standard function in Greenplum, > or how to creat the wrapper function. > > regards > > On Fri, Jul 19, 2013 at 7:47 AM, guxiaobo1982 wrote: > > There is another situation, > > > We have a de

Re: Reply: [GENERAL] Can we specify transaction level when connectting toexternal postgresql server via postgres_fdw

On Fri, Jul 19, 2013 at 7:47 AM, guxiaobo1982 wrote: > There is another situation, > > We have a demo table with about 1700 rows, the "select count(1) from > demotable" statement finishes with-in 3 seconds when executed directlly > against the Greenplum database,but it takes about 230 secon

Re: [GENERAL] Update big table

On Mon, Jul 15, 2013 at 6:08 AM, Haiming Zhang wrote: > I run my query using JOIN for two hours, and did not get it done. Here is > my query: > > update table1 set col1 = true from table2 where table1.event_id = > table2.event_id > Did you already post the query plan from "EXPLAIN update table1

Re: [GENERAL] decrease my query duration

On Thu, Jul 4, 2013 at 5:26 PM, David Carpio wrote: > Hello All > > I am newbie in this forum that's why I will be very grateful if would be > able to help me and if you need some additional information please let me > know. > I have a query that I would like decrease its duration > Have a loo

Re: [GENERAL] Database performs massive reads when I'm doing writes.

On Thu, Jun 6, 2013 at 9:53 AM, Ray Cote wrote: > Starting a few days ago we started to see a strange behavior where writing > to the database causes massive read operations. > For example, I have a table that needs to be updated every night (about > 20,000 rows). > Using Django ORM, we update the

Re: [GENERAL] search_path for database vs user vs session

On Mon, May 27, 2013 at 12:16 PM, Moshe Jacobson wrote: > Oh, and I'd also like to see the current setting of the database so I know > what will happen if I clear the user setting. I think you can find some of what you are looking for here: http://www.postgresql.org/docs/9.2/static/view-pg-sett

Re: [GENERAL] Random numbers

On Sat, May 25, 2013 at 8:45 AM, Karel Riveron Escobar < kesco...@estudiantes.uci.cu> wrote: > I want to generate random numbers in Pl/pgSQL. How can I do this? > To be more specific, I have to generate random numbers among 0 and 5. > One way: select n from unnest(ARRAY[0,1,2,3,4,5]) n order by r

Re: [GENERAL] bloating vacuum

On Tue, May 14, 2013 at 7:51 AM, S H wrote: > I created small table and started doing insertion/deletion/updation on > 2 rows in infinite loop. It started bloating around 844 times, but after it > stopped bloating.. what could be the reason? > Did autovacuum kick in and clean up the table? Is

Re: [GENERAL] Error installation of openmolar

Those are not postgresql errors. Have you tried the openmolar mailing list at https://groups.google.com/forum/?fromgroups#!forum/openmolar ? On Mon, Apr 22, 2013 at 6:05 PM, elmekki mahdhaoui wrote: > Hello everyone > > I tried to install openmolar on my ubuntu > luc

Re: [GENERAL] View to show privileges on views/tables/sequences/foreign tables

On Thu, Feb 21, 2013 at 9:38 AM, bricklen wrote: > A while back I was looking for a way to display object privileges > quickly with a bit better readibility. The following view is what I > came up with. Suggestions and improvements welcome (or comments > stating that there are much

[GENERAL] View to show privileges on views/tables/sequences/foreign tables

A while back I was looking for a way to display object privileges quickly with a bit better readibility. The following view is what I came up with. Suggestions and improvements welcome (or comments stating that there are much easi\er ways to get the same details). (was created in a utility "admin"

  1   2   3   >