http://blog.gmane.org/gmane.comp.db.postgresql.admin hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://comments.gmane.org/gmane.comp.db.postgresql.admin/36830 <pre>Hi all, I have noticed a strange behaviour regarding WAL sequence numbers. I am populating a new DB with old data. To populate the largest part of the data I use wal_level=minimal and archive_mode=off. Then I stop the database and set wal_level=hotstandby and archive_mode=on. After I do that, I notice that the WAL sequence number "rewinds" a little bit, overlaping with the old ones... Is this normal? </pre> German Becker 2013-05-21T18:28:22 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36827 <pre>Hi, everyone. I have a doubt. I have a 32-bit postrgesql running with 2.5gb of shared_buffer. And I have maintenance_work_mem = 1gb and autovacuum_max_workers = 3. How maintenance_work_mem is related to shared_buffer? If the 3 workers uses 1gb, will the database crash? Or their memory usage are separated from each other? </pre> Rodrigo Barboza 2013-05-21T15:07:15 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36821 <pre>Hi Everybody, Has anyone ran into issues running pg_restore? It seems that between 8.3.8 and 9.0.5, 9.1.3 the behavior of pg_restore has changed. Previously I was able to have several data owners with their own schemas and running a pg_restore as one superuser was able to restore the objects in those schemas without an issue. At 9.0.5, I found that I had to restore each schema of a data owner separately. At 9.1.3, I found that in addition to that I need to make each of those data owners superusers. I am fully aware that the dev work for core replication was occurring at this time, but I have been unable to find any documentation about the potential changes to the basic pg_restore functionality. Anyone else noticed or had issues with this? Sincerely, Kasia </pre> Kasia Tuszynska 2013-05-20T22:57:22 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36820 <pre>Hi again, We've got a production cluster running Postgres 9.2.3(-1.30.amzn1 using the exact Amazon package version). I'd like to inspect the buffer_cache usage on this machine but can't get the exact -contrib version from the Amazon repos. I've got a different cluster running 9.2.4 with the contrib package installed. Would it be safe/work to use the pg_buffer_cache extension from there as a drop in on the 9.2.3 host or is that looking for disaster? (The last thing I want now) Doing a ldd on the extension doesn't show specific dependencies but I assume that's not all I need to look into. Kind regards, Armand </pre> Armand du Plessis 2013-05-20T17:24:48 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36816 <pre>Howdy, Environment: Postgres 8.4.2 Gentoo 2.0.1 Server crash over the weekend. After crash customer noticed table missing and when trying to re-create they got error: ERROR: type "solar_promotion_query" already exists Line: 1 _ To resolve they did: BEGIN; update pg_type set typname = 'solar_promotion_query_id_seq_old' where typrelid = 5434806; update pg_type set typname = 'solar_promotion_query_old' where typrelid = 5434808; COMMIT; Everything seems to be fine but wondering if the above action could cause any future issues with relations or any other problems? Thank you, Samuel Stearns </pre> Samuel Stearns 2013-05-20T01:32:36 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36812 <pre>Hi there, We started seeing 1000s of messages like the ones below in our logs starting last night. There's been no changes but performance has dropped significantly. It's Postgres 9.2.3 2013-05-19 12:50:30.423 UTC,,,1976,,5198ca96.7b8,1,,2013-05-19 12:50:30 UTC,8/0,0,DEBUG,00000,"autovacuum: processing database ""dddd_production""",,,,,,,,,"" 2013-05-19 12:50:30.434 UTC,,,1976,,5198ca96.7b8,2,,2013-05-19 12:50:30 UTC,8/159746,1173975322,DEBUG,00000,"transaction ID wrap limit is 2297797644, limited by database with OID 17671",,,,,,,,,"" Any ideas what would be causing this? Kind regards, Armand </pre> Armand du Plessis 2013-05-19T12:54:34 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36811 <pre>hello, i update my server from sarge to wheezy (stable). (dist-upgrade) All package is ok... thanks aptitude !!! The problem is postgresql-9.1, during the installation i read this error... Configurazione di postgresql-9.1 (9.1.9-1)... [....] Starting PostgreSQL 9.1 database server: main[....] Use of uninitialized value $logsize in numeric gt (&gt;) at /usr/bin/pg_ctlcluster line 215. Use of uninitialized value $logsize in numeric gt (&gt;) at /usr/bin/pg_ctlcluster line 215. Use of uninitialized value $logsize in numeric gt (&gt;) at /usr/bin/pg_ctlcluster line 215. Use of uninitialized value $logsize in numeric gt (&gt;) at... ................ invoke-rc.d: initscript postgresql, action "start" failed. dpkg: errore nell'elaborare postgresql-9.1 (--configure): il sottoprocesso installato script di post-installation ha restituito lo stato di errore 1 Si sono verificati degli errori nell'elaborazione: postgresql-9.1 I searched on Google but have not found a solution. HELP ME ! Thanks. FerX </pre> Fernando ff77 2013-05-17T19:03:42 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36809 <pre>I am basically familiar with postgreSQL (in older incarnations) but have not really done a streaming replication (WAL) setup before. In planning for my setup, I am going to use table partitioning and tablespaces to keep things as quick as "possible". However, in a streaming replication environment, will the slave and the master have to be _exactly_ the same in terms of tablespaces? Is it possible some TBSPACE01 on the master be on /sda1 and the same TBSPACE01 on the slave be on /sdb1? Will I have to set up identical hardware and identical drives and directory structure between the master and slave? Thanks. - Victor Tan </pre> Victor Tan 2013-05-16T20:36:11 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36803 <pre>Hi everybody, this is my first message in this list. The company where i work is bringing maintenance service of PostgreSQL to another company, and currently they have installed PostgreSQL 9.1.1, and they want to move to 9.3 version when it will come out. So, because the difference of versions, and because it was installed by compiling it (using source code), and because the 9.1.1 installation is in a different directory than the default, they decided to replace 9.1.1 version with 9.3 (no upgrade, but replace it). Currently, they only have one database in production of 2.2 GB with some procedures and triggers. So, my plan to execute this database installation is the next: 1. Install PostgreSQL 9.3 from postgresql repository (yum.postgresql.org) with a different port to avoid interrupt the production PostgreSQL instance operation 2. Tune the database parameters in postgresql.conf, also create the same rules in pg_hba as the production instance, configure log and so on. 3. At the end of th</pre> Oscar Calderon 2013-05-16T18:04:54 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36800 <pre>Hi, we have a postgresql 9.0, I setup collectd to gather some information. It frequently ask the database about the bgwriter state by requesting "select * from pg_stat_bgwriter" (among other stats). It seems that this requets takes 6-7s to answer and in the log we got "pgstat timeout" Note: The database is constantly loaded with insert (but not overloaded). Does someone have an idea why such request can take so long to answer. I was thinking bgwriter stats is just a matter of picking global info in memory. Can it be due to locks of some sort ? Cordialy, Nicolas Zin </pre> Nicolas Zin 2013-05-14T20:57:06 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36796 <pre>Dear Team, We are using Postgres 9.2 in suse linux 11 Enterprise server. Recently we have migrated from 8.3 to 9.2 directly using pg_dump and pg_restore as we are not using much postgres extensions. Restore was successful and our application running fine. But in some inserts where we use like *to_timestamp('8-5-2013 22:00:02','dd-mm-yyyy hh:mi:ss')::TIMESTAMP* it throws error as hour "22" is invalid for the 12-hour clock. But the same insert is working with version 8.3 Only after modifying the to_timestamp with 24hour clock i.e., *to_timestamp('8-5-2013 22:00:02','dd-mm-yyyy hh24:mi:ss')::TIMESTAMP*, its working fine in version 9.2 Is there any other workaround available instead of modifying multiple queries as lot of our application coding has this conversion. Thanks in advance. Thanks, Franc. </pre> Hariraman Jayaraj 2013-05-13T11:04:14 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36795 <pre>Hi all, I have been trying to setup Postgres 9.2 in HA using streaming replication and base backup. There is no problem in switching from: Standby -&gt; Master using the trigger file mechanism provided by postgres. The problem comes when switching from: Master -&gt; Stanbdy : I try to set up Streaming replication from the new standby to the new Master, but replication doesn't start, rather i find the following error in postgres logs "FATAL: timeline 2 of the primary does not match recovery target timeline 1". Is there any way the timeline can be bumped up to the correct number on the new standby, without taking base backup. And is it safe to use the method described in http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-timeline-switch-of-slave-node-without-archives/ to bump up the timeline. regards, Prakhar </pre> prakhar jauhari 2013-05-13T07:23:13 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36788 <pre>Hi all, My name is Monica Miu and I work in Talent Acquisition for Asurion Mobile Applications a San Mateo (and also SF) based, nimble, semi-autonomous division of Asurion, a privately held multi-billion business services provider. We are looking for an experienced and accomplished Sr. Postgres DBA to join our team as a full-time employee. We offer a very attractive BASE + BONUS + STOCKS + FULL BENEFITS for the position. Asurion Mobile is at the cutting edge of technology in mobile applications and data services, providing millions of customers with complete protection of their personal mobile environments, including data (pictures, video, contacts and more), applications, and even the device itself. The division already has over 5 million subscribers and this opportunity will give the selected candidate the chance to claim that he/she has one of the most broadly distributed mobile applications in the industry. 95% of all insured cell phones in the US are insured by Asurion (but usually white labeled throu</pre> Miu, Monica 2013-05-10T00:01:43 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36787 <pre>Hello Everyone, I have a primary / hot standby scenario with streaming replication. Postgres version is 9.1.8. A full backup is made nightly on the primary and copied to the secondary, just for backup purposes, the replication is never stopped /restarted. Supose that for a reason I need to restore the primary to a point in time, let's say just after the last backup. I've done this by: 1) stoping de databese 2)restoring the backup 3) creatng a recovery.conf where I specified a recovery_target accordingly 4) start the database This works fine on the primary. The quesion is how do I restart the replication on the secondary, on the new timeline? I tried to restore the same backup on the secondary and starting the continuous recovery, but it starts on the previous timeline. I also tried to set the recovery_target_timeline in the secondary to the new timeline, but I get an error. Do I need to get a new copy of the primary to continue the replication? </pre> German Becker 2013-05-09T15:45:55 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36785 <pre>Hi, guys. I started accdentally a recovery with archive mode on and stand by server off. It created a lot of xlog files and filled my whole disk. If I decided to delete xlog files by hand, how do I know which one to delete? </pre> Rodrigo Barboza 2013-05-08T23:24:46 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36775 <pre>I noticed postgresql would do vacuum to prevent wraparound of the transaction id, and I also tried to use vacuum freeze, I am not quite sure about freeze, but it seems postgresql will still vacuum the frezzed unchanged tables automatically. My scenario is, I have a table with daily partitions, those partition table inherits the parent table, with date check. Each day there is tens of millions rows, and old partitions won't be updated, they may be treat as readonly table. Meanwhile, there is hot-stanby server, we treat it as a readonly db. when we query on that db, we often got version conflict error, even on the old partitions. Is there any way better to deal this? </pre> Haifeng Liu 2013-05-08T04:41:28 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36763 <pre> I tried to create make backup my postrgresql cluster with amanda. And I had to create database superuser. I tried to create no-superuser role with only "replication" privillege, but after run backup I got: --8&lt;---------------cut here---------------start-------------&gt;8--- 2013-05-06 13:33:02 CEST ERROR: must be superuser to switch transaction log files 2013-05-06 13:33:02 CEST STATEMENT: SELECT file_name from pg_xlogfile_name_offset(pg_switch_xlog()) --8&lt;---------------cut here---------------end---------------&gt;8--- So my question is: Can I establish postgresql role which is _not_ superuser and can execute above statement? KJ PS. does news.postgresql.org work? I got 'unknown host' </pre> Kamil Jońca 2013-05-07T02:38:39 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36744 <pre>Just out of curiousity, I see comments like this all the time: I've been participating in newsgroups since UUCP days, and I've never encountered a group before that encouraged bottom posting. Bottom posting has traditionally been considered rude -- it forces readers to scroll, often through pages and pages of text, to see a few lines of original material. The most efficient strategy, one that respects other members' time, is to briefly summarize your point at the TOP of a posting, then to *briefly* quote only the relevant parts of the post to which you are replying, and bottom-post after the quoted text. That lets your reader quickly see if it's relevant or not, and move on to the next post. Contributors in these newsgroups seem to think it's OK to quote five pages of someone else's response, then add one or two sentences at the bottom ... it's just laziness that forces readers to wade through the same stuff over and over in each thread. How did the Postgres newsgroups get started with this "only bott</pre> Craig James 2013-05-06T18:15:09 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36734 <pre>Hi, guys. Postgres doc says it is better to place the pgstat.stat file in ram disk. And it says that at shutdown, it is copied to global. What happens if postgres do not shutdown the usual way? If machine loses power, for example... </pre> Rodrigo Barboza 2013-05-03T15:09:00 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36731 <pre>Hello, Usually I would create user uuu password 'ppp'; GRANT usage on schema zzz to uuu; GRANT select on all tables in schema zzz to uuu; But just this morning someone used create user uuu password 'ppp'; alter user uuu set default_transaction_read_only = on; GRANT select on all tables in schema zzz to uuu; So I only added the grant usage and it worked fine. What do people use day to day? I had frankly never explored the default_transaction_read_only parameter ... </pre> matthias ritzkowski 2013-05-03T14:03:02 http://comments.gmane.org/gmane.comp.db.postgresql.admin/36730 <pre>Hi everyone, Every day, the file pg_hba.conf in my server is rewritten and postgresql received a SIGHUP to reload configuration. The pg_hba.conf content is: ============================================= ....... ...... local samerole all md5 host samerole all 127.0.0.1 255.255.255.255 md5 local all postgres md5 host all postgres 127.0.0.1 255.255.255.255 md5 ============================================= As a result, my application cannot connect remotely to the database on server. I have to manually modify the pg_hba.conf every day. I don't know why that happens? Is there any way to get rid of it? Thank you very much for your help. ---------------------------------------------------- Nghia T. Truong </pre> Nghia Truong 2013-05-03T13:45:01 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.postgresql.admin