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

you can use the psql \drds command: \drds [PATRN1 [PATRN2]] list per-database role settings Jov blog: http:amutu.com/blog 2013/5/28 bricklen > > 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 dat

Re: [GENERAL] How to check if Postgresql files are OK

> It really depends. Having multiple backups over time will limit the > risk that corruption gets propagated to a slave system. Also, there is > a CRC on the WAL records which are shipped, which helps a bit, but there > are still cases where corruption can get you. The best thing is to have > fr

Re: [GENERAL] How to check if Postgresql files are OK

there is a wiki page aouble corruption detection： http://wiki.postgresql.org/index.php?title=Corruption_detection but I think avoid corruption is more important and practical than try to check corruption： http://blog.ringerc.id.au/2012/10/avoiding-postgresql-database-corruption.html Jov blog: h

Re: [GENERAL] Most efficient way to initialize a standby server

On 05/27/2013 08:13 PM, Edson Richter wrote: I think the use of PITRTools is probably up your alley here. JD Assume I know nothing about PITRTools (which I really don't know!), can you elaborate a bit more your suggestion? It is an open source tool specificaly for working with PITR/Stream

Re: [GENERAL] How to check if Postgresql files are OK

Nikhil, * Nikhil G Daddikar (n...@celoxis.com) wrote: > We use PostgreSQL 9 on our production server and I was wondering if > there there is a way to know when pages get corrupted. It's not great, but there are a few options. First is to use pg_dump across the entire database and monitor the PG

Re: [GENERAL] How to evaluate disk space needed by a table

On Tue, May 28, 2013 at 9:48 AM, 高健 wrote: > Hello: > > I created a table, and found the file created for that table is about 10 > times of that I estimated! > The following is what I did: > > postgres=# create table tst01(id integer); > CREATE TABLE > postgres=# > > postgres=# select oid from p

[GENERAL] How to check if Postgresql files are OK

Folks, I was using PostgreSQL 8.x in development environment when one day I started getting all kinds of low-level errors while running queries and eventually had to reinstall. Maybe it was salvageable but since it was a test database anyway it didn't matter. We use PostgreSQL 9 on our produ

Re: [GENERAL] How to evaluate disk space needed by a table

* 高健 (luckyjack...@gmail.com) wrote: > So , Is there any method to correctly evaluate disk space one table will > need, > given the table's column data types and , estimated record numbers ? The simplest might be to do exactly what you did- create the table and then check the size with a subset

[GENERAL] How to evaluate disk space needed by a table

Hello: I created a table, and found the file created for that table is about 10 times of that I estimated! The following is what I did: postgres=# create table tst01(id integer); CREATE TABLE postgres=# postgres=# select oid from pg_class where relname='tst01'; oid --- 16384 (1 row) Then

Re: [GENERAL] Most efficient way to initialize a standby server

Em 28/05/2013 00:03, Joshua D. Drake escreveu: On 05/27/2013 05:43 PM, Sergey Konoplev wrote: Try this step-by-step instruction https://code.google.com/p/pgcookbook/wiki/Streaming_Replication_Setup. I constantly update it when discovering useful things, including low bandwidth issues. On Mon,

Re: [GENERAL] Most efficient way to initialize a standby server

Em 27/05/2013 21:43, Sergey Konoplev escreveu: Try this step-by-step instruction https://code.google.com/p/pgcookbook/wiki/Streaming_Replication_Setup. I constantly update it when discovering useful things, including low bandwidth issues. Thanks. This is a good idea, of course! I also have a lo

Re: [GENERAL] Most efficient way to initialize a standby server

On 05/27/2013 05:43 PM, Sergey Konoplev wrote: Try this step-by-step instruction https://code.google.com/p/pgcookbook/wiki/Streaming_Replication_Setup. I constantly update it when discovering useful things, including low bandwidth issues. On Mon, May 27, 2013 at 5:08 PM, Edson Richter wrote:

Re: [GENERAL] Most efficient way to initialize a standby server

Try this step-by-step instruction https://code.google.com/p/pgcookbook/wiki/Streaming_Replication_Setup. I constantly update it when discovering useful things, including low bandwidth issues. On Mon, May 27, 2013 at 5:08 PM, Edson Richter wrote: > Since 9.0 days I do use script with rsync for tra

[GENERAL] Most efficient way to initialize a standby server

I've two distant servers I would like to configure async replication between. Servers are running 9.2.4. Since 9.0 days I do use script with rsync for transfer. And sometimes the servers get out of sync (due large processing in master database and huge network latency), and I have to reinitia

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] search_path for database vs user vs session

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/27/2013 12:16 PM, Moshe Jacobson wrote: > On Mon, May 27, 2013 at 3:07 PM, Moshe Jacobson > mailto:mo...@neadwerx.com>> wrote: > > I'd like to know how to see the search_path setting attached to a > particular user/role independent of the sessi

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

On Mon, May 27, 2013 at 3:07 PM, Moshe Jacobson wrote: > I'd like to know how to see the search_path setting attached to a > particular user/role independent of the session. > 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.

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

On Mon, May 27, 2013 at 2:47 PM, Adrian Klaver wrote: > Is the below what you are looking for? > > http://www.postgresql.org/**docs/9.2/static/runtime-**config-client.html > > http://www.postgresql.org/**docs/9.2/static/function

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

On 05/27/2013 11:29 AM, Moshe Jacobson wrote: How can I show the value of search_path for the database, the user and the session? I ask because I cannot explain the following: $ psql -U postgres -d ises psql (9.1.4) Type "help" for help. postgres@moshe=>devmain:ises=# show searc

[GENERAL] search_path for database vs user vs session

How can I show the value of search_path for the database, the user and the session? I ask because I cannot explain the following: $ psql -U postgres -d ises psql (9.1.4) Type "help" for help. postgres@moshe=>devmain:ises=# show search_path; search_path --- public, audit_log (

[GENERAL] How to know the exact time to which the database was recovered

Hello, We use the basebackup + WAL files strategy to backup our database i.e. take a basebackup every day and copy WAL files to a remote server every 15 minutes. In case of a disaster on the master, we'd recover the database on the slave. If this happens, I would like to tell the customer the

[GENERAL] Understanding postgresql logs

I have a PostgreSQL 9.0/9.2 which from time to time hits some memory issues. I know the best approach is to monitor the DB performance and activity but in the log files I see error messages similar to: TopMemoryContext: 221952 total in 17 blocks; 7440 free (41 chunks); 214512 used TopTransactio

Re: [GENERAL] Success stories of PostgreSQL implementations in different companies

Thank you Wolfgang, just one question, what "bio" means? In the part that says "69 bio EUR..." Regards. Sent from my BlackBerry® wireless device from Telecom. -Original Message- From: Wolfgang Keller Sender: pgsql-general-owner@postgresql.orgDate: Mon, 27 May 2013 17:15:41 To: Subject

Re: [GENERAL] Success stories of PostgreSQL implementations in different companies

> Even with that, some clients are being encouraged to change to > PostgreSQL to lower their companies costs in technologies, but very > often they ask if there are success stories of PostgreSQL > implementations in companies in our region or around the world, > success stories (if is possible) wit

[GENERAL] Document Management System (DMS)

Hi there I am looking for an open-source document management system (DMS) based on PostgreSQL. Anyone has experience with such tools? Thanks & cheers, Peter

Re: [GENERAL] Seq Scan cost shown to be (cost=10000000000.00..10000000001.10)

> When one uses “enable_” settings to adjust planner behavior, PostgreSQL > just sets really high costs for the operations affected (like the one you > see). > > As SeqScan is the only possible way to execute your query, it is still > choosen. > I get it. Thanks! -- Amit Langote -- Sent via pg

Re: [GENERAL] Seq Scan cost shown to be (cost=10000000000.00..10000000001.10)

>> Although, I suspect the (dropped index + enable_seqscan) causes this, >> is the cost shown in explain output some kind of default max or >> something like that for such abnormal cases? > > When you set enable_xxx=off, it not actually disables the xxx > operation, it sets the start cost to the hi

Re: [GENERAL] Seq Scan cost shown to be (cost=10000000000.00..10000000001.10)

2013/5/27 Amit Langote > Although, I suspect the (dropped index + enable_seqscan) causes this, > is the cost shown in explain output some kind of default max or > something like that for such abnormal cases? > When one uses “enable_” settings to adjust planner behavior, PostgreSQL just sets real

Re: [GENERAL] Seq Scan cost shown to be (cost=10000000000.00..10000000001.10)

On Mon, May 27, 2013 at 12:42 AM, Amit Langote wrote: > I set enable_seqscan=off and also accidentally dropped the only index [...] > Seq Scan on testdata (cost=100.00..101.10 rows=2 width=71) [...] > Although, I suspect the (dropped index + enable_seqscan) causes this, > is the

[GENERAL] Seq Scan cost shown to be (cost=10000000000.00..10000000001.10)

Hello, I set enable_seqscan=off and also accidentally dropped the only index on a table (actually, drop extension pg_bigm cascade) and observe following: postgres=# explain select * from testdata where name like '%gi%'; QUERY PLAN -