headers
Virupaksha Kanjilal | 22 May 2013 23:30
Picon
Gravatar

WAL files required to make base backup consistent

Hi

I perform a pg_basebackup with tar option and do not bring in the WAL segments created during the backup. Now I want to make the backup a consistent copy and for that I need the WAL segments. How can I know which all WAL segments to apply to bring the backup to a consistent state?

Thanks
Viru
Permalink | Reply | 1 comment |
headers
German Becker | 21 May 2013 20:28
Picon

WAL files not following sequence

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?
Permalink | Reply | 5 comments |
headers
Rodrigo Barboza | 21 May 2013 17:07
Picon

Question about maintenance_work_mem and shared_buffer

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?
Permalink | Reply | 4 comments |
headers
Kasia Tuszynska | 21 May 2013 00:57
Favicon

pg_restore

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

Permalink | Reply | 3 comments |
headers
Armand du Plessis | 20 May 2013 19:24
Favicon
Gravatar

pg_buffer_cache - dependencies

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
Permalink | Reply | 0 comments |
headers
Samuel Stearns | 20 May 2013 03:32
Picon
Picon

Update pg_type possible problems

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] Script lines: 20-33 ------------------------
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

 

Permalink | Reply | 0 comments |
headers
Armand du Plessis | 19 May 2013 14:54
Favicon
Gravatar

Transaction ID wrap limit is log entries

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
Permalink | Reply | 7 comments |
headers
Fernando ff77 | 17 May 2013 21:03
Picon

Error installal postgresql-9.1

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 (>) at /usr/bin/pg_ctlcluster line 215. Use of uninitialized value $logsize in numeric gt (>) at /usr/bin/pg_ctlcluster line 215. Use of uninitialized value $logsize in numeric gt (>) at /usr/bin/pg_ctlcluster line 215. Use of uninitialized value $logsize in numeric 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
Permalink | Reply | 0 comments |
headers
Victor Tan | 16 May 2013 22:36
Picon

Streaming replication and partitions

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
Permalink | Reply | 1 comment |
headers
Oscar Calderon | 16 May 2013 20:04
Favicon

Migration of server

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 the operations day, create a backup of the production database and then restore it into the new instance
  4. Test the new instance with the PHP applications that use it and verify that all is in order
  5. Stop the old instance and change the port to another port, then change the port of the new instance to 5432 in order to avoid change the network configuration, permissions and so on.

But really is the first time that i do that, so i don't know if i'm missing something or there's something wrong about i'm planning to do, so i will appreciate very much if you can guide me about what steps i have to do exactly and considerations during this process.


Regards.

***************************
Oscar Calderon
Analista de Sistemas
Soluciones Aplicativas S.A. de C.V.
www.solucionesaplicativas.com
Cel. (503) 7741 7850
Permalink | Reply | 5 comments |
headers
Nicolas Zin | 14 May 2013 22:57
Favicon

select * from pg_stat_bgwriter slow

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

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Permalink | Reply | 0 comments |

Gmane