[GENERAL] Re: [GENERAL] 8.4 – Where, when, what...
Where: What is the best way to get a copy of the 8.4 beta? I've searched around, but couldn't find a reliable direction on this subject. http://yum.pgsqlrpms.org/news-8.4devel-ready-for-testing.php Note these are development RPMs. When: Is there any official word on when 8.4 will be released? We are working on an enterprise solution with a release date in May and as such, the release date for 8.4 is an important consideration in our planning. I think targeted date is May '09 and may be delayed beyond that since there is no beta yet. But it's worth the wait. I'm a postgresql end user so I'm guessing here. What: Is there a more or less definitive list of the new features that will end up in the release? There are 3 features that influenced our decision to go with 8.4 and I would like to read more about the level of certainty with which these will end up in 8.4. The features are 1) WITH clauses, 2) WINDOW-ing functions like SUM() OVER PARTITION BY (), LEAD(), LAG(), etc., and 3) Hierarchical queries. http://momjian.us/main/writings/pgsql/features.pdf Joey
Re: [GENERAL] No MD5SUM for 8.1.16 RHEL5 rpms
On Wed, Feb 11, 2009 at 3:05 PM, Devrim GÜNDÜZ dev...@gunduz.org wrote: Hi, I downloaded RHEL5 rpm files for the latest 8.1.16 release and could not find the MD5 file for them. http://ftp9.us.postgresql.org/pub/mirrors/postgresql/binary/v8.1.16/linux/rpms/redhat/rhel-5-i386/ 8.1.15 RHEL5 roms had them, http://ftp9.us.postgresql.org/pub/mirrors/postgresql/binary/v8.1.15/linux/rpms/redhat/rhel-5-i386/ How do I get the checksum for these files to compare? Until they sync to FTP sites, you can check the signature of the RPMs. They are signed with: http://yum.pgsqlrpms.org/RPM-GPG-KEY-PGDG You can import this to your rpm database and check packages with rpm --checksig . Thanks Devrim! That worked. Joey
[GENERAL] No MD5SUM for 8.1.16 RHEL5 rpms
Hello, I downloaded RHEL5 rpm files for the latest 8.1.16 release and could not find the MD5 file for them. http://ftp9.us.postgresql.org/pub/mirrors/postgresql/binary/v8.1.16/linux/rpms/redhat/rhel-5-i386/ 8.1.15 RHEL5 roms had them, http://ftp9.us.postgresql.org/pub/mirrors/postgresql/binary/v8.1.15/linux/rpms/redhat/rhel-5-i386/ How do I get the checksum for these files to compare? Thanks, Joey
[GENERAL] Switch off PITR
Hello all, I have been doing PITR backups for a while to a backup server via NFS. It's working great. I have to shutdown the backup server for hardware/OS upgrade. I expect this upgrade will last a week. How do I turn off PITR in the mean time? I commented archive_command and issued a pg_ctl reload and postgres is *still* archiving logs to the backup server. I'm running 8.1.5 and scheduled for upgrade to latest 8.1.x once the backup server is upgraded. Thanks in advance, Joey
[GENERAL] Question about VACUUM
Hello all, I inherited a 8.1.x database of size 200GB. This database hasn't been maintained much (autovac is enabled though). I been trying to VACUUM this db for the past few days for a couple of hours/day. The server runs 24x7 so continuous maintenance is not possible (this is the last resort). My questions are, Is it possible to estimate how long VACUUM on a table might take? The table size is growing as VACUUM is being performed. I assume I need reindex after VACUUM is complete. I run VACUUM from psql and I Ctrl-C it to turn it off is this acceptable? maintenance_work_mem is at 64MB and shared_buffers at 2GB. Should I dedicate more memory to maintenance_work_mem to speedup VACUUM? The server is a Intel Xeon 3.0GHz with 4GB RAM and RAID-5 (Yes I know). Thanks in advance, Steve
[GENERAL] ]OT] Parsing postgresql.conf archive_command
Hello,
I'd like to get the path configured for archive_command in a shell script
I've used a file with following content as an example (postgresql.conf),
# archive_command = cp %p /backup/%r
# archive_command = cp %p /backup/%r
# archive_command = cp %p /backup/%r
#archive_command = cp %p /backup/%r
archive_command = 'cp %p /backup/wal/%f'
This is what I been trying.
$ awk '!/[ \t]*#/ { sub(/%f$/, , $NF); print $NF }' postgresql.conf
and I get
/backup/wal/%f'
Any idea how to get rid of %f' so that I get only?
/backup/wal/
My regexp skills are sad :-)
Steve
[GENERAL] access public relation from a private database
Hello, I've created a table in public schema, CREATE TABLE public.foo (fooid SERIAL); When I try to create a table in database nowhere that references public.foo table, CREATE DATABASE nowhere; \c nowhere; CREATE TABLE bar (bar integer REFERENCES public.foo(fooid)); I get, ERROR: relation public.foo does not exist. Can I reference public relations from private database? Thanks, Steve
[GENERAL] ]OT] Database structure question
Hello, I'm not a DBA nor an architect. I learn from the wise and by making mistakes. I'm in the process of developing a web application and need some advice from you all, Requirements: == * Application runs 24/7 * Application must support either PostgreSQL or another commercial enterprise database (the suits want this) * Application supports 100's of customers on the server and few users (~25) per customer * Application can support only one customer if needed * Application will have audit data * Some data is common to all customers (default values, help information) * Hibernate will be used for database abstraction * Performance more important than disk usage * Max database size per customer will be ~18GB with 25000 rows and ~40 tables Some Design thoughts (I could have gotten a few facts wrong): Multiple schema in one database vs Multiple databases in one cluster: This has probably been talked several times on the list. I'm convinced about multiple databases in a cluster because, (a) Cross database support (b) Database for X customer can be removed or moved to another server easily (c) Audit data can be separate schema on the same database (d) Data security by default (Pg does not allow cross database access) (Ex: cust1 connects to db1 and so on. user1, user2 in db1) (e) Simple design, flexible and easy to maintain (vacuum per database) Why i dont want multiple schema per database (i) Database owner can access all schema by default (2) Multiple schema for same customer (say audit) becmes complex (3) Can get complex with grants/revokes soon for fine-grained permissions (4) If application gets compromised, possibility of more data exposure (dbuser connects to database with multiple schemas for customers. If dbuser is compromised, all schema in the datbase is compromised?) (5) Schema may not be well supported on other databases? Storing binary in database vs filesystem: === This again has been probably beated to death. Application will store less frequently used binary files on filesystem and more frequently used binary data in the database, Binary data is ~300-400KB. Compressing text data: = I'd like the text data (a few KB's) that could be stored as TEXT compressed during inserts to save diskspace. This will impose CPU overhead vs IO which is acceptable. LZO compression could be appropriate (speed vs size). thoughts? A DBA will review requirements and propose a design. In the mean time, I wanted to take a stab at this. Steve
[GENERAL] Getting cozy with weekly PITR
Hello, Thanks for advice/suggestions on PITR so far. We have finally decided to do weekly PITR base backups. Just have one nagging question. == week 1==- * pg_start_backup() * tar -cvzf basebackup_week1.tar.gz pgdata/ * pg_stop_backup() cp WAL1 week1/wal/ .. cp WAL2 week1/wal/ ==*== Week 1, we perform base backup and WAL will be backed up by PG. ==week 2== * pg_start_backup() * tar -cvzf basebackup_week2.tar.gz pgdata * pg_stop_backup() cp WAL1 week2/wal/ ... cp WAL2 week2/wal ... rm -f basebackup_week1.tar.gz rm -rf week1/wal ==*== During week 2, after the base backup, can we remove week 1's base and WAL files? I assume this is okay since week 2's PITR backup is as good as new but just wanted to be doubly sure about it before we get rid of week 1's backup. Steve
[GENERAL] PITR and base + full backups
Hello, Just to be sure of our backups we plan to do a base + full backups (yes, we are overly paranoid) (1) pg_start_backup(`date`) (2) perform hot rsync first (while the database is running) $ rsync -avr pgdata /backup/`date`/ (3) stop pg (4) perform cold rsync $ rsync -avr --delete pgdata /backup/`date`/ (5) start pg (6) pg_stop_backup() This didn't work and not sure if this is supposed to work ;-) Or should I stick to just plain PITR? Thanks, Steve
Re: [GENERAL] bug on ALTER TABLE
On Wed, May 14, 2008 at 4:35 PM, Martin Marques [EMAIL PROTECTED]
wrote:
Please, can someone explain how is it posible for ALTER TABLE to add a
primary key column to a table without some intruction that would make it a
real PK (NOT NULL and UNIQUE).
prueba= CREATE TABLE nopk (
prueba( textito varchar
prueba( );
CREATE TABLE
prueba= INSERT INTO nopk VALUES ('algo de texto');
INSERT 0 1
prueba= INSERT INTO nopk VALUES ('otro texto');
INSERT 0 1
prueba= ALTER TABLE nopk ADD COLUMN id INT PRIMARY KEY;
NOTICE: ALTER TABLE / ADD PRIMARY KEY creará el índice implícito
«nopk_pkey» para la tabla «nopk»
ALTER TABLE
prueba= \d nopk
Tabla «martin.nopk»
Columna | Tipo| Modificadores
-+---+---
textito | character varying |
id | integer | not null
Índices:
«nopk_pkey» PRIMARY KEY, btree (id)
prueba= SELECT * FROM nopk WHERE id IS NULL;
textito| id
---+
algo de texto |
otro texto|
(2 filas)
So id is a pk with NULL values, which isn't right.
This bug recently fixed in the 8.4 branch
http://archives.postgresql.org/message-id/[EMAIL PROTECTED]
http://wiki.postgresql.org/wiki/CommitFest:May
I'm not sure if this will be backported.
Joey
Re: [GENERAL] Can´t connect but listen address and pg_hba configured correctly
On Thu, Apr 10, 2008 at 1:26 PM, Marcelo de Moraes Serpa [EMAIL PROTECTED] wrote: I can´t connect to my postgresql8.1 server running on Debian. The pgadmin client says it can't connect. I already edited the pg_hba.conf and postgresql.conf (listen_addresses = '*' and port) but the problem remains, pg_admin is running on a XP machine without Firewalls enabled, Debian is also not running any kind of firewall. I should also note the I just upgraded to 8.1, some minutes ago I was running 7.4 and connections **were working fine**. pg_hba.conf: http://www.pastebin.ca/980122 postgresql.conf (connection settings section): http://www.pastebin.ca/980147 PostgreSQL 8.1 Debain Etch package. Any hints greatly appreciated! Your postgresql.conf has port set to 5433 (PostgreSQL default port is 5432). Did you change the port in pgadmin to connect to 5433? Joey
[GENERAL] Limiting postgresql resources
Hello, Is it possible to limit cpu/memory resources to queries on postgres something akin to limits.conf on Linux? We have a couple of databases (pg 8.2.7) serving 500K - 1M records. For various reasons, users are allowed to run queries against the database via a web based query interface. These queries are mostly used to generate reports. Some times users write inefficient queries that brings the entire database server (runs webserver as well) to it's knees affecting others users on the system. So, is it possible to kill a query thats running for x minutes with 50%+ CPU load and using y MB of memory? In a perfect world, we would address such issues in the application design/database design and/or hardware. But our application is in an imperfect world where such resources are unavailable. Thanks in advance, Joey
Re: [GENERAL] Need help on how to backup a table
On Thu, Mar 27, 2008 at 11:05 PM, ajcity [EMAIL PROTECTED] wrote: Hi all, I am trying to backup a large table with about 6 million rows. I want to export the data from the table and be able to import it into another table on a different database server (from pgsql 8.1 to 8.2). I need to export the data through SQL query 'cause I want to do a gradual backup. Does pgsql have a facility for this? Thanks in advance for your reply. Have you looked at pg_dump -t http://www.postgresql.org/docs/8.2/static/app-pgdump.html Joey
Fwd: [GENERAL] Survey: renaming/removing script binaries (createdb, createuser...)
-- Forwarded message -- From: Joey K. [EMAIL PROTECTED] Date: Wed, Mar 26, 2008 at 9:42 AM Subject: Re: [GENERAL] Survey: renaming/removing script binaries (createdb, createuser...) To: Zdeněk Kotala [EMAIL PROTECTED] Please let us know your meaning, thanks Zdenek Kotala 1) What type of names do you prefer? --- a) old notation - createdb, createuser ... b) new one with pg_ prefix - pg_createdb, pg_creteuser ... c) new one with pg prefix - pgcreatedb, pgcreateuser ... d) remove them - psql is the solution e) remove them - pgadmin is the solution [b] 2) How often do you use these tools? --- a) every day (e.g. in my cron) b) one per week c) one time d) never [a] In cron from commandline. in shell scripts 3) What name of initdb do you prefer? -- -- a) initdb b) pg_initdb c) pg_init d) pg_ctl -d dir init (replace initdb with pg_ctl new functionality) e) What is initdb? My start/stop script does it automatically. [c] 4) How do you perform VACUUM? - a) vacuumdb - shell command b) VACUUM - SQL command c) autovacuum d) What is vacuum? [a], [b] and [c] Depending on the situation. For example ifcustomers complain, we manually do cia SQL. If this is a consistent problem, we usr cron for vacuumdb. If the database is not too loaded and supports autovacuum, we also have autovacuum enabled. We'd like to keep vacuumdb but maybe pg_vacuumdb? Hope this helps. Joey
Re: [GENERAL] identify database process given client process
On Mon, Mar 17, 2008 at 6:58 AM, hogcia [EMAIL PROTECTED] wrote: Hi, I have to find a Postgres database process pid (or other identification) for a given client process pid. Or client processes for a database process. How are they connected? I was suggested maybe netstat could give me the answer and I think those are two pf_unix processes. But maybe there are some PostgreSQL functions that do this? How should I approach this topic? Thanks in advance, Try select pg_stat_activity; Joey
Re: [GENERAL] Seeking datacenter PITR backup procedures [RESENDING]
I'm guessing you're in a hurry or in a pinch that you need to repost after one day on a weekend. I was waiting to let someone more knowledgeable answer, but I've had some experience with this, so I'll answer to the best of my ability. I apologize. I wasn't sure if my first email ended up as spam since I saw other posts getting through. I'll be patient the next time ;-) We have several web applications with Pg 8.2.x running on isolated servers (~25). The database size on each machines (du -h pgdata) is ~2 GB. We have been using nightly filesystem backup (stop pg, tar backup to ftp, start pg) and it worked well. Any reason why you haven't been using pg_dump? There are a LOT of drawbacks to doing filesystem level backups. For example, you can't restore to disparate hardware (a filesystem backup made from PG on an i386 system won't work on an amd64 system, for example) We have used pg_dump and like it. The drawback is that it is excruciatingly slow for backups. Although our databases are ~2GB currently, they will grow to 6~10 GB per database in the next 6 months and 25~30GB in a year. Our hardware configuration is managed well and we do not see us switching architectures often. If we do, we will be performing a pg_dump/restore at the time. We would like to move to PITR backups since the database size will increase moving forward and our current backup method might increase server downtimes. How much do you expect it to increase? 2G is _tiny_ by modern standards. Even if you expect it to increase an order of magnitude, it's still a reasonable size for pg_dump. Some huge advantages to pg_dump: *) architecture-neutral dumps *) No need to stop the database *) Extremely simple procedure for backup and restore *) Human parseable backups (you may not even need to restore, just look through the data to see what was there in some cases) *) Can restore a database without shutting down a server, thus you can move a database from one server to another (for example) without affecting work occurring on the second server. We have used pg_dump on an offline database. If pg_dump is performed on a running database, will the recovery be consistent? ** START ** tmpwal = /localhost/tmp # tmp space on server 1 for storing wal files before ftp Configure $pgdata/postgresql.conf archive_command = cp %p $tmpwal/%f . Recovery on server1 (skeleton commands), % rm -f $tmpwal/* Why are you removing this day's WAL logs before recovery? If the disaster occurs close to your backup time, this will result in the loss of an entire day's data changes. Thanks for pointing this out. . % cp -r pgdata.hosed/pg_xlog pgdata/ % echo cp $tmpwal/%f %p pgdata/recovery.conf % start pg (recovery begins) ** END ** Again, how much WAL traffic are you generating? Make sure you have enough free space on the recovery system to hold all of the WAL logs in the event you need to recover. How do I measure the WAL traffic generated? Is this the size variance of pg_xlog per day? We plan our database size to diskspace ratio to be 1:1.5 on the servers. Note that this procedure does not do a good job of protecting you from catastrophic hardware failure. In the event that your RAID system goes insane, you can lose as much as an entire day's worth of updates, and there's no reason to. Currently, a day's worth of data loss is an accepted risk. Once we get our infrastructure upgraded (moving away from FTP to NFS for these servers) it will make our backups near realtime. I _highly_ recommend you stage some disaster scenarios and actually use your procedure to restore some databases before you trust it. Getting PITR working effectively is tricky, no matter how many questions you ask of knowledgeable people. You're going to want to have first- hand experience going through the process. Absolutely. We will run tests to ensure the process works. Sharing experience implementing PITR in an environment like ours might help avoid some initial pitfalls. Joey K
[GENERAL] Seeking datacenter PITR backup procedures [RESENDING]
Greetings, We have several web applications with Pg 8.2.x running on isolated servers (~25). The database size on each machines (du -h pgdata) is ~2 GB. We have been using nightly filesystem backup (stop pg, tar backup to ftp, start pg) and it worked well. We would like to move to PITR backups since the database size will increase moving forward and our current backup method might increase server downtimes. We have a central ftp backup server (yes, ftp :-) which we would like to use for weekly full and daily incremental PITR backups. After reading the docs, PITR is still fuzzy. Our ideas for backup are (do not worry about the syntax), ** START ** tmpwal = /localhost/tmp # tmp space on server 1 for storing wal files before ftp Configure $pgdata/postgresql.conf archive_command = cp %p $tmpwal/%f Day 1: % psql pg_start_backup(); tar pgdata.tar --exclude pg_xlog/ pgdata % psql pg_stop_backup() % ftp put pgdata.tar ftpserver:/server1/day1/pgdata % ftp put $tmpwal/* ftpserver:/server1/day1/wal % rm -f $tmpwal/* pgdata.tar Day 2: % ftp put $tmpwal/* ftpserver:/server1/day2/wal % rm -f $tmpwal/* Day 3: ... ... Day 7: % rm -f $tmpwal/* Start over Recovery on server1 (skeleton commands), % rm -f $tmpwal/* % mv pgdata pgdata.hosed % ftp get ftpbackup:/server1/day1/pgdata.tar . % tar -xvf pgdata.tar % ftp get ftpbackup:/server1/day1/wal/* $tmpwal % ftp get ftpbackup:/server1/day2/wal/* $tmpwal . . % cp -r pgdata.hosed/pg_xlog pgdata/ % echo cp $tmpwal/%f %p pgdata/recovery.conf % start pg (recovery begins) ** END ** Assumptions: a. After pg_stop_backup(), Pg immediately recycles log files and hence wal logs can be copied to backup. This is a clean start. b. New wal files since (a) are incremental backups We are not sure if WAL log filenames are unique and possibly overwrite older wal files during recovery. I'm seeking suggestions from others with experience performing PostgreSQL PITR backups from multiple servers to a central backup server. Thanks in advance, Joey Krane
[GENERAL] Seeking datacenter PITR backup suggestions
Greetings, We have several web applications with Pg 8.2.x running on isolated servers (~25). The database size on each machines (du -h pgdata) is ~2 GB. We have been using nightly filesystem backup (stop pg, tar backup to ftp, start pg) and it worked well. We would like to move to PITR backups since the database size will increase moving forward and our current backup method might increase server downtimes. We have a central ftp backup server (yes, ftp :-) which we would like to use for weekly full and daily incremental PITR backups. After reading the docs, PITR is still fuzzy. Our ideas for backup are (do not worry about the syntax), ** START ** tmpwal = /localhost/tmp # tmp space on server 1 for storing wal files before ftp Configure $pgdata/postgresql.conf archive_command = cp %p $tmpwal/%f Day 1: % psql pg_start_backup(); tar pgdata.tar --exclude pg_xlog/ pgdata % psql pg_stop_backup() % ftp put pgdata.tar ftpserver:/server1/day1/pgdata % ftp put $tmpwal/* ftpserver:/server1/day1/wal % rm -f $tmpwal/* pgdata.tar Day 2: % ftp put $tmpwal/* ftpserver:/server1/day2/wal % rm -f $tmpwal/* Day 3: ... ... Day 7: % rm -f $tmpwal/* Start over Recovery on server1 (skeleton commands), % rm -f $tmpwal/* % mv pgdata pgdata.hosed % ftp get ftpbackup:/server1/day1/pgdata.tar . % tar -xvf pgdata.tar % ftp get ftpbackup:/server1/day1/wal/* $tmpwal % ftp get ftpbackup:/server1/day2/wal/* $tmpwal . . % cp -r pgdata.hosed/pg_xlog pgdata/ % echo cp $tmpwal/%f %p pgdata/recovery.conf % start pg (recovery begins) ** END ** Assumptions: a. After pg_stop_backup(), Pg immediately recycles log files and hence wal logs can be copied to backup. This is a clean start. b. New wal files since (a) are incremental backups We are not sure if WAL log filenames are unique and possibly overwrite older wal files during recovery. I'm seeking suggestions from others with experience performing PostgreSQL PITR backups from multiple servers to a central backup server. Thanks in advance, Joey Krane
