Re: [GENERAL] Queries seldomly take 4s while normally take 1ms?
Christian -- original text snip because this POS editor won't let me properly edit postgres version ? type of replication ? changes from postgres config defaults ? Do they happen more at peak usage, semi regularly or sporadically ? Possibly some sporadic postgres process such as checkpoints of autovac processes kicking off. Do your logs show anything ? HTH, Greg W. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near :
Graham -- From: Graham Leggett minf...@sharp.fm To: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Wednesday, March 6, 2013 2:41 PM Subject: [GENERAL] ERROR: syntax error at or near : Hi all, I have a text file, and I need to update the value of an element in a table with the contents of this text file. Following the instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-file I tried this, but get the error below, which I do not understand. Can anyone explain what might be going wrong, and what I should do instead? patricia=# \set content `cat /tmp/certificates.txt` patricia=# update property set value = :'content' where key = 'patricia.home.security.cacerts'; ERROR: syntax error at or near : LINE 1: update property set value = :'content' where key = 'patricia... ^ The colon (:) is not needed, just remove it. A pair of colons is used to indicate a cast of a value; off hand I am not coming up with any use of a colon in basic SQL. Greg W. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near :
Thanks for the link / explanation -- hadn't seen this use before. GW - Original Message - From: Adrian Klaver adrian.kla...@gmail.com To: Greg Williamson gwilliamso...@yahoo.com Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Wednesday, March 6, 2013 3:13 PM Subject: Re: [GENERAL] ERROR: syntax error at or near : On 03/06/2013 03:04 PM, Greg Williamson wrote: Graham -- From: Graham Leggett minf...@sharp.fm To: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Wednesday, March 6, 2013 2:41 PM Subject: [GENERAL] ERROR: syntax error at or near : Hi all, I have a text file, and I need to update the value of an element in a table with the contents of this text file. Following the instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-file I tried this, but get the error below, which I do not understand. Can anyone explain what might be going wrong, and what I should do instead? patricia=# \set content `cat /tmp/certificates.txt` patricia=# update property set value = :'content' where key = 'patricia.home.security.cacerts'; ERROR: syntax error at or near : LINE 1: update property set value = :'content' where key = 'patricia... ^ The colon (:) is not needed, just remove it. A pair of colons is used to indicate a cast of a value; off hand I am not coming up with any use of a colon in basic SQL. http://www.postgresql.org/docs/9.2/interactive/app-psql.html#APP-PSQL-INTERPOLATION Greg W. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database schema
Dhiraj -- From: Dhiraj Gupta dhira...@nic.in To: pgsql-general@postgresql.org Sent: Sunday, December 2, 2012 9:35 PM Subject: [GENERAL] Database schema Hi All, I have created a database name 'ofbiz. then the default schema name public created automatically. I want to create schema name ofbiz in the database ofbiz when I create database name ofbiz then the schema name ofbiz will create automatically. how it is possible if yes, Thanks Dhiraj Gupta When a new database is created, the template is the builtin database called template1. If you create the schema in that database, that schema will be made part of any new database you make. You could also make a new template database, create the schema in that one, and then create your new databases using the WITH TEMPLATE = option in CREATE DATABASE. So if you have need of different schemas with different databases, that would be more extensible by making new templates, onjhe3 for each type./. HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)
Kevin -- You wrote: ... running transactions can cause autovacuum processes to stall out or be autocancelled. Long running transactions - is now long? In our system it's rare to have a transaction (even a prepared transaction) last much longer than a few minutes. Is that enough time to cause problems with AutoVacuum? The only situation where I would expect that to be a problem is in a very small table which is updated hundreds of times per second. Could you elaborate on this, or point me to a previous thread ? I've got precisely such a database; currently we do an hourly reindex on all user tables in addition to some fairly standard autovac settings. The tables themselves don't seem to bloat much during ordinary operations but if we don't reindex performance tanks; when the site has issues we sometimes see table bloat but it seems to be dealt with by autovac. This is a postgres 9.1 instance w/ SSDs, lots of RAM (24 gigs) and relatively small tables (maybe a few thousands of rows in most cases, total footprint of the database is 240 megs) and being updated in the low thousands of times per second. Thanks! Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum)
Scott -- ... To tune autovacuum with 50 databases, start by dropping nap time to something much lower, like 10s. Then if you need to, drop cost delay until you get to 0. If you get to 0 and it's still not hitting your IO too hard, but not keeping up, then increase cost limit. If you get to something in the 5000 to 1 range, and its still not keeping up then start bumping the thread count Thanks for outlining a strategy on this -- useful advice. Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dropdb breaks replication?
Edson -- I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? Sorry for the slow response -- as others have indicated, the drop db is probably not the problem. We have one system that drops a several-gig database hourly and the replication has never failed. We see issues on the master with dead file handles but the replication itself is rock solid. Greg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL and WMS/WFS Service
You might look at the GIS extension, PostGIS: http://postgis.refractions.net/ Not sure how much yu need to do, but a company I used to work for ran a WMS service off of an earlier version of postGIS. Buena Suerte! Greg Williamson From: José Pedro Santos zpsant...@hotmail.com To: Postgres Ajuda pgsql-general@postgresql.org Sent: Tuesday, October 9, 2012 8:37 AM Subject: [GENERAL] PostgreSQL and WMS/WFS Service Dear all, How can I set up a service for a layer stored within postgres like WMS or WFS? I have one WEBGIS Framework that only allow data with that input. Many thanks. Best Regards, José Santos -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PGBouncer Connection Using Perl DBI
Prashant -- I haven't tried this -- we don't use DBI currently -- but wouldn't it get treated as any other connection if you point the DBI connection to the pgbouncer host / port / database ? pgbouncer should redirect the query according the rules in its .ini file. HTH, Greg Williamson From: Prashant Bharucha prashantbharu...@yahoo.ca To: pgsql-general@postgresql.org Sent: Sunday, August 26, 2012 6:51 PM Subject: [GENERAL] PGBouncer Connection Using Perl DBI Hello , Could you please tell me some one ,how to do connection PGBouncer using Perl DBI module ? Thanks Prashant
Re: [GENERAL] Run external SQL file via Perl dbh
Toby --
A small point:
...
How about something like
use File::Slurp;
use Try::Tiny;
try {
$dbh-being_work;
$dbh-begin_work;
...
Clarification for any who might be unfamiliar with the perl tools.
Greg Williamson
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Populate Table From Two Other Tables
Rich -- I want to combine columns in two tables and use the exported resulting table for statistical analyses. The SQL script is: INSERT INTO waterchem (site, sampdate, param, quant, ceneq1, low, high, stream, basin) SELECT c.site, c.sampdate, c.param, c.quant, c.ceneq1, c.low, c.high, s.stream, s.basin FROM chemistry as c, sites as s WHERE c.site == s.siteid; Try a single equals sign, e.g. WHERE c.site = s.siteid The problem is that both c.site and s.siteid are of type VARCHAR(16) and postgres tells me, ERROR: operator does not exist: character varying == character varying and provides the hint to add explicit type casts. Since the string length of site/siteid varies I don't see how to cast both to a working type. Please suggest how I can populate this table while avoiding the operator error. HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Does Postgres compress data?
Mike -- ... Is PG compressing this data? I'm curious as I was considering converting this column to a byte array and gzip'ing the data to save space, however if PG is already doing this for me, then I'm not going to bother. Thanks! Mike It may vary from version of postgres to version, but perhaps you are seeing the effects of TOAST kicking in ? Do a search in the documentation for your specific version (8.3, 9.1 etc.) HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] trimming a column
Michael -- Hi, I need to trim whitespace off of a whole column and replace the existing values with the trimmed ones. This isn't working update mytable set id = trim(id); I'm not sure of the correct syntax. Help appreciated. Mike The trim function needs to be told what sort of trim to do -- Following the 9.1 manual (you did not specify which version of postgres you are using) try: UPDATE mytable SET id = trim(both ' ' from id). (untested) See http://www.postgresql.org/docs/9.1/static/functions-string.html HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
Filip Rembiałkowski suggested: On Thu, Mar 15, 2012 at 6:43 AM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: Is there any way to consolidate the pages on the slave without taking replication offline? maybe CLUSTER? ... Of course events destined to this table will be queued by Slony while the table is locked. I've not much recent experience with Slony, but possibly pg_reorg, found at: http://pgfoundry.org/projects/reorg/ would be of use ... Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] version controlling postgresql code
akp geek asked: Would like to know if any one of you have used CVS or some other version controlling tools to version the postgres code? Any recommendations? Appreciate your help I used CVS at previous jobs. Currently the place I work uses git and a set of rules for programmers who create DDL changes, including rollback options (such as renaming columns to dropme_xxx instead of simply dropping them outright in the first change set). The SQL files are checked in with a timestamp which controls the order of updates when we apply them to runtime. I've had unpleasant experiences with commercial tools; CVS works well for simple environments and git can be used for more tangled development work. HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Measuring replication lag time
Stuart Bishop shaped the aether to ask: Hi. I need to measure how far in the past a hot standby is, async streaming replication. Not sure if this will help, but we are using repmgr https://github.com/greg2ndQuadrant/repmgr; it sets up a monitoring schema which we poll )see the Monitoring and Testing section ... study their source code some and see how they come up with lag times. HTH, Greg WiIliamson DBA Powerreviews dot com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump schma while excluding specific table
Tony -- When I issue: pg_dump newdb /DUMPDIR/newdb.dmp -n dev -T corgi -w -v -F c 2 /DUMPDIR/newdb.log I get a dump of the entire dev schema. My goal is to dump the dev schema minus the corgi table. How can I adjust my script to perform this function? Thanks. Maybe the order of your arguments is causing some issues. This worked for me pg_dump -s -T product_feed_data staging_feed sf.sql Where staging_feed is that database and product_feed_data is the table I excluded: grep product_feed_data sf.sql | grep -v product_feed_data_ COMMENT ON TABLE product_key IS 'A temporary table used to sync product_feed_data.does_exist_in_product. ... HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Add quto increment to existing column
Robert --
Hi,
I have a column in a table called hist_id with the datatype integer. When I
created the table I assigned this column the primary key constraint but didn´t
make it an auto-increment column.
How could I do this to an the already existing column?
I have created the sequence with the following command but don´t know how to
change the existing column to auto-increment.
$ create sequence hist_id_seq;
Perhaps:
ALTER TABLE history_foo ALTER COLUMN hist_id SET DEFAULT nextval('hist_id_seq');
?? (untested)
You may need to to update the sequence to reflect your current highest value
for hist_id (or whatever the column is -- set the sequence using select
setval('hist_id_seq', maxid) so that the next value you get from the sequence
doesn't collide with existing values.
HTH,
Greg Williamson
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SPAM Alert !
This is clearly spam and I would suggest that the sender be blacklisted for sending it. Sure, they might be a victim too, but one whose mail system has been compromised. From: Chris Barnes compuguruchrisbar...@hotmail.com To: chr...@bitheads.com; lindsay.d...@btconnect.com; s.li...@sympatico.ca; sue.montgom...@arnprioraerospace.com; recr...@uohs.uottawa.ca; shabanabuwa...@mergisgroup.ca; tracy.ingletonda...@scotiabank.com; pgsql-general@postgresql.org; marketing-noti...@enterprisedb.com Sent: Thursday, August 25, 2011 1:57 AM Subject: [GENERAL] http://cfanorthhills.com/newslet.php -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Postgresql as application server
Dear Postgres users, snip How about sending these to just one mailing list -- when you cross post everybody gets two copies of each response. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup Restore a database in PostgreSQL
Siva -- Thanks a lot for your reply. As usual Backup worked perfectly. When I tried restore using the command you provided, I got the below list of errors! Please help me out on this. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of function public.plpgsql_call_handler Command was: DROP FUNCTION public.plpgsql_call_handler(); Clue #1 -- you are not running as the correct user for the restore into this database. pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to drop procedural language Command was: DROP PROCEDURAL LANGUAGE plpgsql; Clue #2! So you need to change to being the postgres user that created the database, which presumably has the proper permissions to drop these various entities. snipped redundant error messages? HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I need your help to get opinions about this situation
Rayner -- ... I have a database of 1000 tables, 300 of theirs are of major growing with 1 rows daily, the estimate growing for this database is of 2,6 TB every year. In and of-itself sheer number of rows only hits you when you need to be reading most of them; in that case good hardware (lots of spindles!) would be needed for any database. There are accessing 5000 clients to this database of which will be accessed 500 concurrent clients at the same time. That could be too many to handle natively; investigate pgPool and similar tools. There are the questions: 1.Is capable PostgreSQL to support this workload? Some examples better than this. Depends on the native hardware and the types of queries. 2.It is a recommendation to use a cluster with load balancer and replication for this situation? Which tools are recommended for this purpose? Depends on what you mean -- there is no multimaster solution in postgreSQL as far as I know, but if you only need one central servers and R/O slaves there are several possible solutions (Slony as an add-on as well as the new capabilities in the engine itself. 3.Which are the hardware recommendations to deploy on servers? CPU, RAM memory capacity, Hard disk capacity and type of RAID system recommended to use among others like Operating System and network connection speed. RAID-5 is generally a bad choice for databases. The specific answers to these questions need more info on workload, etc. I migrated a fairly large Informix system to postgres a few years ago and the main issues had to do with postGIS vs. Informix Spatial Blade; the core tables converted cleanly; the users and permissions were also easy. We needed to use pgPool to get the same number of connections. This was actually a migration -- from Sun Solaris to Linux so comparing the two directly wasn't easy. We moved chunks on the application and tested a lot; spatial data first and the bookkeeping and accounting functions and finally the warehouse and large-but-infrequent jobs. HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Importing/Appending to Existing Table
Rich -- I have an existing table with 15,492 rows and want to add additional rows from a .csv file. If I use 'COPY tablename from filename with delimiter as : csv quote as ' ' will this overwrite existing rows in the table or append rows? It will not overwrite any existing data; if the table has constraints that will prevent duplicates then the entire load will fail if any item fails (it is a single transaction). If the table doesn't have such constraints then duplicate data in the copy file will result in duplicate rows. HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Comparison of Oracle and PostgreSQL full text search
zhong ming wu wrote: On Tue, Jul 27, 2010 at 9:58 PM, Howard Rogers h...@diznix.com wrote: For what it's worth, I wrote up the performance comparison here: http://diznix.com/dizwell/archives/153 I always thought there is a clause in their user agreement preventing the users from publishing benchmarks like that. I must be mistaken. Perhaps not as I remember such issues a few years when the company I worked at profiled postgres against Oracle. Oracle doesn't want poorly-tuned systems being used as benchmarks. Or so they claim. Our tests -- very much oriented at postGIS found Oracle to be between 5 and 15% _faster_ depending on the specifics of the task. We decided to go with postgres given the price difference (several hundred thousand dollars for Oracle in the configuration we needed vs. zip for postgres -- we already had trained postgres DBAs). YMMV. Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to auto-increment?
See the data type SERIAL in the PostgreSQL manual for whatever flavor of the database you are using ... Apologies for top-posting -- challenged mail client. HTH, Greg W. From: Andre Lopes lopes80an...@gmail.com To: pgsql-general@postgresql.org Sent: Wed, December 2, 2009 2:52:51 PM Subject: [GENERAL] How to auto-increment? Hi, I have a table like this: id_product id_increment and I need to increment values in id_increment like this prod_1 1 prod_1 2 prod_1 3 prod_2 1 Wich is the best way to do this? Using a trigger? Where can I find examples of plpgsql doing this? Best Regards, André. Sorry for my bad english.
Re: [GENERAL] Server Backup: pg_dump vs pg_dumpall
Hi -- I'm writing a backup script. Right now, I only have one database on my postgresql server. I'm deciding if I should use pg_dump or pg_dumpall when backing up the server. As far as I can tell, pg_dumpall cannot compress the dumps automatically and it only dumps data in the standard SQL text file format. This means that I would not be able to use pg_restore to selectively restore the database, correct? What is *NOT* included in a pg_dump that IS in pg_dumpall (Other than all the databases)? Things like user-defined functions and datatypes? Roles? Views? The pg_dumpall gets users and groups that are not dumped in the single database versions; I used to use pg_dump on each of several databases on a given server and then pg_dumpall with suitable parameters to get only those bits and nothing else. User defined stuff, views etc. are all in a given database so they will be in the pg_dump. HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a meaningful benchmark?
Dann Corbit wrote: Here is another interesting benchmark with a particular user's application: http://blog.page2rss.com/2007/01/postgresql-vs-mysql-performance.html P.S. Oracle won't let you publish any benchmark numbers. So if you find an Oracle comparison, it's unauthorized True enough. That said I feel comfortable in revealing that a former employer of mine ran some serious tests of PostgreSQL vs Oracle (with an emphasis of postGIS and Oracle's equivalent) about a year and a half ago. Oracle was consistently 5-15% faster depending on the precise benchmark. This was judged to be not worth the extra money for more Oracle licenses. In some other environments that edge might be worth the money. Oracle does spend resources on its products and so I don't find the speed difference surprising. But when you consider the speed with which Oracle produces patches vs. the Postgres folks the winner is clearly the latter. Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High cpu usage after many inserts
--- On Wed, 2/25/09, Jordan Tomkinson jor...@moodle.com wrote: ... What exactly is wrong with RAID5 and what should we have gone with? RAID10 is often used. As others have pointed out, it is very slow for random writes. It also has issues that expose your data to total loss, see for instance http://www.miracleas.com/BAARF/RAID5_versus_RAID10.txt. HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What makes a Postgres DBA?
Merlin Moncure wrote: On 11/4/07, Kevin Hunter [EMAIL PROTECTED] wrote: Following up on a recent thread (http://archives.postgresql.org/pgsql-general/2007-11/msg00064.php) ... Next question and one that I'm not sure how to phrase: how does one become a Postgres-savvy* DBA? Just by working with it as a developer and then moving up the ranks? (i.e working with larger, more active setups/datasets?) There's the training options listed on http://www.postgresql.org/about/eventarchive, but I'm personally skeptical at just how much one can retain from a 1-to-5 day course. I would suggest that in order for someone to get that most coveted of titles 'PostgreSQL DBA' on should: Administration: * Understand tradeoffs of autovacuum and scheduled vacuum (increasingly, this question will morph into autovacuum configuration and monitoring) * Understand backups: when to use pitr, pg_dump * Basic configuration: shared_buffers, etc etc * Be able to do task specific tuning: understand difference between oltp and olap environments * Be versatile with psql shell (most of the best DBAs use psql almost exclusively) * No how to troubleshoot and deal with locking problems and runaway queries * Should be able to setup postgresql manually without using packages * Be able to implement a replication technology (Slony) * Understand some of the specific challenges involved in dealing with large databases, which I define as over 10 times the memory in the server. * Understand the challenges involved with 24/7 environments * Understand the differences in low and high security environments and how to set up for both Development * Should be very comfortable with pl/sql, pl/plpgsql (pl/perl or alternative is bonus) * Understand triggers, views, functions, rules, constraints, domains and type and be able to give examples of when they should be used (or not) * Understand good normalization strategies * Be able to discuss pros and cons of surrogate keys * Understand MVCC * C language experience * Strong familiarity with bash or perl (preferably both) Personal * A DBA often anchors a development team. 'Works well with others' is a cliché, but still applies. * At least superficially familiar with the technologies that interface with the database * Be cool and calm under pressure * Be established on the appropriate mailing lists and use good etiquette * Understand that developers are out to get your database merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq An excellent summary ! I would add that the core of being a DBA does have some platform independent aspects. I took some introductory Informix classes years ago on data normalization and the like, which combined with some long-ago computer science classes (data structures, mostly) at UCB to help broaden my understanding. And a lot of OJT and other classes since then, of course. Some of the issues that are general (but which require understanding of each platform's unique aspects). All of these tend to be more meaningful the more you understand about the domain as a whole. Merlin noted them but they are pillars on which a lot of useful databases can be built. * data normalization -- what data goes where * indexing -- what it does, what it helps, what it doesn't help * referential integrity and why it matters (and when it doesn't) * authentication and access issues * locking and related issues * backups and safety nets I particularly like your last points, Merlin, on developers. I've been on both sides of the fence and databases are a specialized animal that usually needs specialized care. Being able to work well with designers and developers is crucial in delivering a useful end result. Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
Richard Broersma Jr wrote: --- Gregory Williamson [EMAIL PROTECTED] wrote: A very low fill factor means that pages are sparse and so inserts and updates are less likely to trigger massive b-tree rebalancings. I take it that massive b-tree rebalancings could cause a problem with the performance of disk writing though-put from UPDATEs and INSERTs? Regards, Richard Broersma Jr. Precisely -- even if it can keep everything in RAM it can occupy quite a few cycles to rebalance a large b-tree. And eventually those changes do need to get written to disk so the next checkpoint (I think) will also have more work. G ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Transactional DDL
Harpreet Dhaliwal wrote: And this feature i.e. transactional DDL is not there in other major RDBMS like sql server, oracle etc? thanks ~Harpreet ...snipped earlier postings... It surprised me when I saw Oracle's behavior. Informix supports DDL within transactions quite happily: create table foo22 (id int,myvalue varchar); create table foo23 (id int); begin; alter table foo22 drop (myvalue); alter table foo23 add (mynewvalue varchar); rollback; INFO - foo22: Columns Indexes Privileges References Status ... Display column names and data types for a table. --- [EMAIL PROTECTED] -- Press CTRL-W for Help Column name TypeNulls id integer yes myvalue varchar(1,1)yes AND INFO - foo23: Columns Indexes Privileges References Status ... Display column names and data types for a table. --- [EMAIL PROTECTED] -- Press CTRL-W for Help Column name TypeNulls id integer yes QED. It's a strong point in PostgreSQL's favor that it behaves in what I regard as a sane manner. That Oracle stuff makes me shudder -- it's unclean. Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
