Re: [ADMIN] move dbs from 8.1 to 8.4

On 10/10/2013 01:17 PM, Marc Fromm wrote: I built a new server running centos 6.4 and postgresql 8.4. I backed up all the databases from the old server running fedora and postgresql 8.1 using this script. *#!/bin/bash* *# Backup all Postgresql databases* ** *# Location of the backup logfi

Re: [ADMIN] RPM 9.1

On 06/04/2013 01:38 PM, dx k9 wrote: http://yum.postgresql.org/repopackages.php Does the CentOS 6 - x86-64 9.1 RPM include the 9.1.9 patch or do you have to go to 9.2 for that rpm? ~DjK If you have loaded this rpm then you can use yum to upgrade to 9.1.9: http://yum.postgresql.org/9.1/redhat

Re: [ADMIN] Big UPDATE breaking replication

On 06/04/2013 04:53 AM, Kouber Saparev wrote: Hello, We are using the 9.1 built-in streaming replication. Recently our slave nodes fell behind because of an UPDATE statement. It took about 3 minutes to execute, but it affected half a million records, hence the replication broke with the "reques

Re: [ADMIN] Copy one database to another server

On 05/02/2013 03:01 PM, Huan Ruan wrote: Hi All We are migrating from Firebird to Postgres. One task we frequently perform is to copy one database from one server to another... I think it would be helpful to know *why* you do this (backup, forensics, development, shifting load among virtual ma

Re: [ADMIN] Postgres 9.1 statistics in pg_stat_database

On 02/07/2013 11:05 AM, Tom Lane wrote: =?utf-8?q?Milos_Gajdos?= writes: What this shows is that xact_commit increases by 3 after just one row insert. tup_inserted does seem to report correct value. After simple SELECT, xact_commit increases by 2 - kind of strange as SELECT shouldn't really b

Re: [ADMIN] Massive table bloat

On 12/11/2012 11:41 AM, Michael Sawyers wrote: Political reasons have ruled out the dump and reload options, but restoring the entire database took several hours. I'm also restricted on version because newer versions of postgres are not supported with that specific product, including maintenance

Re: [ADMIN] Massive table bloat

On 12/11/2012 08:11 AM, Michael Sawyers wrote: Our dba quit last week leaving me with an interesting problem. We have a table currently using 33gb worth of space for only 152mb worth of data because of bad processes or autovacuum not being aggressive enough. I was able to confirm the size differ

Re: [ADMIN] Fwd: Monitoring Replication on Master/Slave Postgres(9.1)

On 11/28/2012 11:24 AM, Shams Khan wrote: Thanks for the response Steve...It was really helpful: Below are some doubts I wanted to clarify..please read and suggest. Can we also check if replication was broken earlier...somehow due to power failure of some other reasons in past...The reason I am

Re: [ADMIN] Fwd: Monitoring Replication on Master/Slave Postgres(9.1)

On 11/28/2012 10:21 AM, Shams Khan wrote: ...how do we ensure my replication is working fine?... Below is the core of one of my bash-script tools. It could use some tweaking (comments welcome) but works well. The script is run every minute by cron on master and standby servers. It auto-deter

Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1

On 09/27/2012 07:01 PM, Greg Williamson wrote: Steve (and others who replied): ... The other is a slimmed-down version of our production database, which gets recreated hourly by a shell script which pulls data from remote servers, does a pg_dump of the resulting 3 gig database, and then drops

Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1

On 09/27/2012 03:05 PM, Greg Williamson wrote: Dear list, I have a postgres database, 9.1.3, which shows a fairly constant amount of space used by postgres, but total disk space kees shrinking. If I restart postgres the space on my file system returns. This cluster is replicated to another; th

[ADMIN] Upgrading and streaming replication

I'm planning my upgrade from 9.1.5 to 9.1.6 on some master and streaming-replication standby pairs and have some questions: Generally: http://www.postgresql.org/docs/9.1/static/warm-standby.html section 25.2.1 says: "...it is likely that running different minor release levels on primary and

Re: [ADMIN] dropping a large table is taking a very long time (about 2 hours so far)

On 09/24/2012 02:10 PM, Evan Walter wrote: There were probaby about 5 million records in the table. I do not believe there were any foreign keys to the table The drop table has been going for over 2 hours. Is that normal? I'd check for locks from other transactions preventing the drop. One po

Re: [ADMIN] changes to postgresql.conf

On 09/24/2012 06:05 AM, Johnny Tan wrote: I need to make changes to these settings in postgresql.conf: * effective_cache_size * shared_buffers * work_mem * maintenance_work_mem On a live, production server, can I simply do a "reload" and get those values in, or do I need to do a stop/start? To

Re: [ADMIN] Postgres Cache usage

On 09/19/2012 09:36 AM, A J wrote: Hi, I have a read heavy application. I would want it to read from memory as database latency has to be in low milliseconds. The database is not too big in size and can be fully contained in memory. With Postgres, if I cache all the tables (by pre-emptive quer

Re: [ADMIN] Messed up time zones

On 08/03/2012 08:23 AM, Laszlo Nagy wrote: ... It works. Thank you! So is it impossible to construct a query with columns that are different time zones? I hope I'm not going to need that. :-) I'm not sure you have internalized the meaning of timestamptz. It helps to instead think of it as

Re: [ADMIN] standby with a fixed lag behind the master

On 07/26/2012 02:24 AM, Alexey Klyukin wrote: Hello, I've recently come across the task of setting up a PostgreSQL 9.1 standby server that is N hours behind the master, i.e. only transactions that finished N hours in the past or older should be replayed on a standby. The goal is to have a kn

Re: [ADMIN] saved passwords lost when connection fails

On 05/26/2012 12:38 PM, Michael Shapiro wrote: PgAdmin doesn't retain the saved passwords when a connection to a server fails. I'd like to request that the saved passwords be kept, even in the event of a failure. I realize that if the failure is due to an incorrect password, there needs to

Re: [ADMIN] introduction

On 05/25/2012 08:05 AM, matthias wrote: Hello everyone, I have some postgres experience, but not so much with clusters/slony Someone is suggesting a HA solution, and I do not have the time to wrap my head around it right know, not to mention support. Are you using clustering for redundancy or

Re: [ADMIN] Backup/disaster recovery and bandwidth (long)

On 04/25/2012 09:11 AM, Scott Whitney wrote: ... My current setup uses a single PG 8.x... My _new_ setup will instead be 2 PG 9.x ... It is best to specify actual major version. While 8.0.x or 9.1.x is sufficient to discuss features and capabilities, 9.1 is a different major release than 9.0, n

Re: [ADMIN] Cannot Connect To Db From Local Server

On 04/18/2012 03:12 PM, Rickard, David wrote: We have a legacy PostgresSQL 7.3.4 db on Solaris That's not legacy, that's antique. I'm basing my commands on recent versions - if the options differed back then you may have to adjust. which has recently (as in last 24 hours or so) begun refu

Re: [ADMIN] utf8 database not dumping utf8 characters

On 04/06/2012 01:10 PM, Matt Williams wrote: With that same dump file that is displaying incorrectly open in vim, I can paste in the utf8 character I provided as an example and it displays correctly. I usually find a good first step is to run the file through something that will give you a hex

Re: [ADMIN] utf8 database not dumping utf8 characters

On 04/06/2012 12:55 PM, Matt Williams wrote: I have a database that is utf8 and displays utf8 values correctly in psql. When dumped, it displays the utf8 characters incorrectly. ie. ö turns into à In the header of the dump file, I have: SET client_encoding = 'UTF8'; So I'm not sure where the

Re: [ADMIN] about encoding

On 03/28/2012 08:00 PM, superman0920 wrote: > hello > i want to insert a report to postgresql，the report contain something > Chinese characters and the postgresql is utf-8. > the response from db is this: > ERROR: invalid byte sequence for encoding "UTF8": 0xb1 > how can i fix it ? > Remove the bad

Re: [ADMIN] pg_attribute file in PostgreSQL 9.0

On 03/07/2012 09:03 AM, Lukasz Brodziak wrote: Thanks a lot I have found file I needed let's hope I will be able to fix the problem now as I cannot connect to the database because I get 'catalog is missing 1 attribute for relation 2662' which I hope to be able to repair by getting the pg_attribut

Re: [ADMIN] pg_attribute file in PostgreSQL 9.0

On 03/07/2012 05:14 AM, Lukasz Brodziak wrote: Hello, I have a question regarding pg_attribute. In which file it is stored because the relfilenode for it shows 0 and file 1249 isn't present in the folder. select pg_relation_filepath('pg_attribute'); From the docs, relfilenode is: "Name of the

Re: [ADMIN] Big difference in databasesize compared with disksize

On 02/17/2012 01:57 AM, Bernhard Schrader wrote: As result of no response i will try again and add some more information for you. The given problem is the same. I have many DB's which have for unknown reason, differences in Filesizes, if you check them with "du" or "ls", in their Filenodes. S

Re: [ADMIN] overcoming a recursive relationship in a sql statement

On 02/01/2012 03:27 PM, brooks.gl...@comcast.net wrote: Hello, ... I'm not sure I fully understand the problem and may, therefore, oversimplify but I'll take a stab. It sounds like you have organizations and organizations can form partnerships with one another. My initial approach would be

Re: [ADMIN] Use a custom postgresql.conf?

On 11/29/2011 01:06 PM, Colin E Busse wrote: Hello, I am trying to determine how to get postgres to use a custom postgresql.conf file that will be located in a directory other than the default postgresql.conf file. I would like it to be installed via an rc script so that the database setup c

Re: [ADMIN] setting timezone

On 11/09/2011 11:41 AM, Geoffrey Myers wrote: Geoffrey Myers wrote: Tom Lane wrote: Steve Crawford writes: On 11/09/2011 05:10 AM, Geoffrey Myers wrote: Is there a way to retain this information from the original database when reloading? Time-zone setting is an attribute of the server

Re: [ADMIN] setting timezone

On 11/09/2011 05:10 AM, Geoffrey Myers wrote: We are moving a number of databases to new hardware. Some of these machines have the timezone set in the database differently then the actual location of the machine as they are access from a different timezone. We were surprised to note that when

Re: [ADMIN] min duration logging

On 10/11/2011 11:23 AM, AA wrote: hey all trying to find non optimized queries and want to use this logging feature, however postgresql seems to write every query to log. my conf is as follows... log_min_duration_statement = 450 ... What durations are showing in the log? Are lots/all of the

Re: [ADMIN] currval()

On 09/20/2011 04:06 PM, Marc Fromm wrote: My postgres version is 8.1. Last I heard RETURNING id started in 8.2. I'd advise upgrading. 8.1 is no-longer supported (end-of-life was last November) and 8.2 has an end-of-life date in just a couple months. You'll get security and bug fixes, performa

Re: [ADMIN] currval()

On 09/20/2011 03:00 PM, Marc Fromm wrote: I am trying to get the id of the current inserted record. The field name is 'id' and it is a primary key. I am obviously missing the correct syntax. I cannot use RETURNING id. $sql = "INSERT INTO jobs (job_title, num_positions, pay_min, pay_max,

Re: [ADMIN] pg_upgrade difficulties

On 09/14/2011 08:48 AM, Steve Crawford wrote: On 09/13/2011 05:31 PM, Tom Lane wrote: Steve Crawford writes: waiting for server to start../usr/pgsql-9.1/bin/pg_ctl: symbol lookup error: /usr/pgsql-9.1/bin/pg_ctl: undefined symbol: PQping There were problems executing "/usr/pgsql-9.

Re: [ADMIN] pg_upgrade difficulties

On 09/13/2011 05:31 PM, Tom Lane wrote: Steve Crawford writes: waiting for server to start../usr/pgsql-9.1/bin/pg_ctl: symbol lookup error: /usr/pgsql-9.1/bin/pg_ctl: undefined symbol: PQping There were problems executing "/usr/pgsql-9.1/bin/pg_ctl" -w -l "upgrade.log"

[ADMIN] pg_upgrade difficulties

I am encountering multiple issues in my attempt to upgrade from PostgreSQL 9.0.4 to 9.1.0 on CentOS 5.6 i386. The latest working version is 9.0.4 installed from the http://yum.pgrpms.org/ site. After installing the 9.1 repo RPM and installing 9.1 via yum I set up a script to do pg_upgrade with

Re: [ADMIN] How frequently to defrag(cluster)

On 07/20/2011 02:04 PM, Steve Crawford wrote: On 07/20/2011 12:58 PM, A J wrote: I understand that 'cluster' performs the role of defrag ... As with everything the answer is "it depends". For a "typical" workload where the rows updated by a single query are one

Re: [ADMIN] How frequently to defrag(cluster)

On 07/20/2011 12:58 PM, A J wrote: I understand that 'cluster' performs the role of defrag (along with rewriting in index order) in Postgres. How frequently does one have to run cluster ? Any thumb-rules or experience ? How do I find if my table is fragmented enough to need a cluster ? We are

Re: [ADMIN] could not open relation with OID

On 07/13/2011 04:32 PM, Tom Lane wrote: Steve Crawford writes: I've seen the "could not open relation with OID" error a couple times recently ... Not worry You might be able to dodge the problem by excluding temp relations from the reporting query, though whether the sum o

[ADMIN] could not open relation with OID

I've seen the "could not open relation with OID" error a couple times recently and Googling for that error gives responses ranging from *very scary* to fuggetaboutit. The error today occurred running this query which is part of a database status reporting script: select pg_size_pretty(sum

Re: R: Re: [ADMIN] Install postgreSQL 9.0 in Linux Ubuntu 9.10

On 07/07/2011 08:29 AM, francescobocca...@libero.it wrote: Hi, i tried to run : /postgresql-9.0.4-1-linux.bin but i received segmentation fault error. So i think, after Steve reply, that the best way to install postgresql 9.0 is to install Ubuntu 10.04. Am i right? Thanks for your help, Best d

Re: [ADMIN] Install postgreSQL 9.0 in Linux Ubuntu 9.10

On 07/07/2011 02:04 AM, francescobocca...@libero.it wrote: Dear all, i tried to install postgresql 9.0 in my Linux Ubuntu 9.10. ... The following packages have unmet dependencies: postgresql-9.0: Depends: libc6 (>= 2.11) but 2.10.1-0ubuntu19 is to be installed Depends: libg

Re: [ADMIN] Who is causing all this i/o?

On 05/20/2011 02:14 PM, Craig James wrote: Our development server (PG 8.4.4 on Ubuntu server) is constantly doing something, and I can't figure out what. The two production servers, which are essentially identical, don't show these symptoms. In a nutshell, it's showing 10K blocks per second o

Re: [ADMIN] unix timestamp

On 04/21/2011 12:19 PM, Marc Fromm wrote: Is there a way to query a unix timestamp date? In the database the orderdate field is a unix timestamp. I would like to create the where clause to a query on a specific date like December 17, 2010. Select * from orders where orderdate = '12/17/2010

Re: [ADMIN] Freebsd/Postgres/Apache+PHP

On 03/31/2011 12:18 PM, babak badaei wrote: Hello Postgres Community, Thank you guys very much for PostgreSQL. To me, its the best database management system under the sun. I am setting up a new jail configuration on FreeBSD with sockets and everything enabled. I can ping localhost fine. The p

Re: [ADMIN] full vacuum of a very large table

On 03/29/2011 08:56 AM, Nic Chidu wrote: Got a situation where a 130 mil rows (137GB) table needs to be brought down in size to 10 mil records (most recent) with the least amount of downtime. Doing a full vacuum would be faster on: - 120 mil rows deleted and 10 mil active (delete most of the

Re: [ADMIN] full vacuum of a very large table

On 03/29/2011 09:04 AM, Plugge, Joe R. wrote: Personally, provided you have the room, I would build a new table off to the side and then migrate what you need to keep to the new table, when done, and satisfied that you have all of the candidate rows, ranem the original to table to "x_tablename"

Re: [ADMIN] pg_restore error

Bryan White wrote: ... The new server is a dual socket Nahalem. 8 cores, 16 threads, 48 GB ram, 2 WAL drives in RAID1 and 12 database drives in RAID10. I would try setting -j higher - in your case try 8 for starters. Also turn off fsync and autovacuum (and turn them back on !!!). See these a

Re: [ADMIN] pg_restore error

Bryan White wrote: I am trying to load a data base using pg_restore.. Is there a known problem with using parallel loading in 8.4 from a file created with an 8.1 database? It was unclear from your email how you are creating the dump. Did you create the dump with the 8.1 or the 8.4 ver

Re: [ADMIN] Just wanted to say thanks for a great tool

So if you have a success-story, consider contributing for the benefit of the project as a case-study, success-story, etc. For ideas/examples, see: http://www.postgresql.org/about/casestudies/ http://www.postgresql.org/about/users http://www.postgresql.org/about/quotesarchive Some of those pages

Re: [ADMIN] WAL file compatibility

Tom Lane wrote: ... The source and destination servers have to be the same major PG release, same architecture (no 32-bit vs 64-bit for instance), and built with the same configure options. OS per se shouldn't matter, but you could easily get burnt on configure options if you use binaries obtain

Re: [ADMIN] pg_attribute size

Anj Adu wrote: I have a few databases where the size of pg_attribute > 6G..This keeps growing.. is there a recommended way to purge data from this table.. Could this also be why tools like pgAdmin take forever to open the database browser? Do you have an extraordinary number of tables/c

Re: [ADMIN] idle connections

Aras Angelo wrote: Thank you for the informative post. We believe this was caused by a network issue yesterday after checking our network speeds. We were maxing our ethernet port at 100 mbps Ah, yes. In a previous life we once had a very successful internal load-test turn into a rapid and

Re: [ADMIN] idle connections

Aras Angelo wrote: ... When i kill the earliest idle process the others stop too. So i dont know whats wrong really. All our apps use the same footer, with pg_close() at the end ... I have done most of the things you guys suggested, so it seems to me that something between php-apache-post

Re: [ADMIN] idle connections

Aras Angelo wrote: Hi, Im seeing lots of idle connections (not idle in transaction) to my database server. My front end is written with PHP and i couldnt find anything that can cause this. If i do a kill proc-id every few minutes on my server via cron, would this effect anything badly? Than

Re: [ADMIN] CSV Utility

Mike angelo wrote: Is there a Postgres utility that allows data contained in a CSV file to be loaded into the database? You mean like psql? \copy tablename from csvfilename CSV or with a header: \copy tablename from csvfilename CSV HEADER or an alternate delimiter: \copy tablename from csvf

Re: [ADMIN] error from postgresql 8.4.0 after a dump/restore from 8.3.5

Kenneth Marshall wrote: I just thought of something, would running an older release of pgadmin cause this error? I have just restored a DB from 8.3.5 to 8.4.0 and I just received the error: ERROR: column "reltriggers" does not exist at character 41 STATEMENT: SELECT relhasindex, relkind,

Re: [ADMIN] Help! Upgrade to 8.4 dropped my databases

Andrew Perrin wrote: Greetings all- Running postgresql under debian, my standard apt-get upgrade upgraded me from 8.3 to 8.4. As a result, I no longer have access to the databases that were created under 8.3. Are you sure that 8.3 is really gone? Check around (look at what is in /etc/init.d

Re: [ADMIN] Database Import..Please Help Me

Premila Devi wrote: Good Day. I like to "Import Database" Using the postgresql-8.3.7-1-windows. But, at here I did not see any "IMPORT DATABASE FEATURES"... The file you attached is empty. We need more info. As a starting point, PostgreSQL dumps are created using pg_dump. The dump f

Re: [ADMIN] Urgent PST time changing tonight

DM wrote: Hi All, PST time changing tonight, I am not sure if this is going to affect the database IF you are up-to-date on your TZ files the database itself should be fine. But external processing is another matter - especially if you have processes that assume conventions from previous

Re: [ADMIN] check to see when tables were last vacummed

Kevin Kempter wrote: Hi all; Anyone know where to look to see when tables were last vacuumed via autovacuum ? Also, can I run this check only in 8.3+ or did it work also in 8.2, 8.1 ? Thanks in advance In 8.3 look in the "pg_stat_user_tables" system table for the last_vacuum and last_a

Re: [ADMIN] primary key and insert

Marc Fromm wrote: I created this table: ... id | integer | not null Indexes: "alert_list_pkey" PRIMARY KEY, btree (id) I get this error when I run the insert a listed below. The insert does not have an entry for the primary key "id" since I thought it upd

Re: [ADMIN] PostgreSQL Database freezes during backup then generates drwtsn.exe process

Ward Eaton wrote: I'm running PostgreSQL 8.3 on a Windows 2003 machine (pgAgent service is also installed and running). The system is running as a redundant database server. Thus, backing up from one machine to another, and transferring over WAL logs. In the postageSQL log files there are

Re: [ADMIN] [HACKERS] Monitoring postgres

Kellyton Campos Feitosa - GYN wrote: Dears, I need monitor a postgres database, but I don’t know which tool to use. The tool need perform the below actions 1. show transactions pendents 2. show the statistics per session actives 3. show the statistics per database 4. show metrics

Re: [ADMIN] What process clears the logs?

Carol Walter wrote: Ah-h-h, that's exactly my question. What part of Postgres "takes care of this itself." I'm asking because I had 86 pg_clog files dated back to mid-May. I got the impression from something Tom said that backups should prune this directory. Perhaps my "impression" was wron

Re: [ADMIN] What process clears the logs?

Carol Walter wrote: Greetings, As you may be aware, we experienced a problem last week with pg_clogs that had been deleted, through human error it appears. What process will clear or delete the pg_clogs? I've been all over the documentation and I'm not finding a reference to this. Postgre

Re: [ADMIN] Do we need vacuuming when tables are regularly dropped?

Tom Lane wrote: Steve Crawford <[EMAIL PROTECTED]> writes: [EMAIL PROTECTED]> CLUSTER pg_class USING pg_class_oid_index ; ERROR: "pg_class" is a system catalog I think the DB is probably protecting you from yourself here ;-). And elsewhere. :) I wasn't ad

Re: [ADMIN] Do we need vacuuming when tables are regularly dropped?

Tom Lane wrote: "Scott Marlowe" <[EMAIL PROTECTED]> writes: Also, there was a time when you couldn't do vacuum full on system tables do to locking issues, and had to take the db down to single user mode to do so. There was a short period when *concurrent* vacuum fulls on just the wrong

Re: [ADMIN] Do we need vacuuming when tables are regularly dropped?

What it sounds like to me is that you're not vacuuming the system catalogs, which are getting bloated with dead rows about all those dropped tables. Wow, great! It is not immediately clear from the documentation, but the VACUUM command also deals with the system catalogs as well, correct

Re: [ADMIN] Hex representation

Scott Marlowe wrote: I used this very simple little php script to make this filename: mk55: #!/usr/bin/php -q Or, using standard *nix tools (Note: 0x55 = ascii U): dd bs=1k count=256 if=/dev/zero | tr '\000' U > full_of_0x55 Cheers, Steve -- Sent via pgsql-admin mailing list (pgsql-admi

Re: [ADMIN] New postgres installation

H. Hall wrote: Carol Walter wrote: It been suggested that I download the binary and just go from there. All the documentation I've read says that for Unix installations should compile source. Would using the binary allow me to do the customization my site requires. I want to put the databas

Re: [ADMIN] New postgres installation

Carol Walter wrote: I'm running Solaris 10 I want to do a new installation of postgres. I have version 8.2.3 and I want to go to 8.3.3 There are pre-compiled binaries for Solaris at: http://www.postgresql.org/ftp/binary/v8.3.3/solaris/solaris10/ Within the appropriate architecture sub

Re: [ADMIN] New postgres installation

Carol Walter wrote: Hello, I want to do a new installation of postgres. I have version 8.2.3 and I want to go to 8.3.3. The postgres documentation says that the default location for the installation is /usr/local/pgsql. My installation has obviously been customized because I have no such p

Re: [ADMIN] Major upgrade advice

Achilleas Mantzios wrote: pg_dump by default ommits OIDs. However why do you want to completely remove OID functionality? space usage? It doesn't backup the OIDs themselves, but it does set "with oids" if the table had them. Per docs (http://www.postgresql.org/docs/8.3/static/runtime-config-

[ADMIN] Major upgrade advice

I'm getting ready to tackle another upgrade: 7.4.6 -> 8.3.3. The preliminaries (development/testing) look good but I'd like suggestions/warnings/comments on: 1. Removing OIDs on user tables. I don't see any dump or restore options to remove OIDs. Am I better off modifying the dump to alter th

Re: [ADMIN] Disk Space issue

Joshua D. Drake wrote: On Wed, 2008-06-11 at 09:36 +1000, Devendra Singh Rawat wrote: Hi, We are using PostgreSQL 7.4.5 and facing issues with increasing disk space ...I would also suggest strongly you update to the latest 7.4 dot release. But as always read all the release n

Re: [ADMIN] Postgres Performance

> I am using postgres 7.0 with linux with 15 GB of database & > 3 gb of RAM. Can anyone suggest what is the best setting > for postgres. I am using postgres as web db. You mentioned elsewhere that you are getting 2,000 hits/day. I don't know the nature of your web app but typical web apps involv

Re: [ADMIN] Postgres Performance

Aftab Alam wrote: Dear All, I am using postgres 7.0 with linux with 15 GB of database & 3 gb of RAM. Can anyone suggest what is the best setting for postgres. I am using postgres as web db. I'm going to assume/hope that you mean 8.0. Even then, you would be well advised t

Re: [ADMIN] [SQL] Documenting a DB schema

Shahaf Abileah wrote: I'm looking for a systematic way to document the schema for the database behind our website (www.redfin.com ), so that the developers using this database have a better idea what all the tables and columns mean and what data to expect. Any recomme

Re: [ADMIN] last analyze / vacuum

Kevin Kempter wrote: Hi List; Is there a system catalog, or other means where I can find out the last time a table was analyzed and the last time a table was vacuumed? Start with: select * from pg_stat_all_tables; Cheers, Steve ---(end of broadcast)---

Re: [ADMIN] All numeric database names?

Chris Hoover wrote: Just wanted to check, are there any problems/gotcha's to having a cluster of all numeric database names? We are thinking of switching our database naming system to use our customer number (a 6 digit integer) for the database name. Is a negative/bad thing? Technically speak

Re: [ADMIN] Enhancement request

Campbell, Lance wrote: Could you please add to your to do list a schema parameter for vacuum? Example: VACUUM SCHEMA xyz; PostgreSQL would get a list of all of the tables found in the schema. It would then loop through vacuuming each table in the schema. I found today that I

Re: [ADMIN] Upgrading from 7.2.1 to 8.x

>> I have been pushing for 3 years to upgrade this database, and with some >> upcoming projects, I am going to do another push with management to upgrade >> this database. > > Push hard. They'll thank you later. No they won't. They have been running this way fine for years - if they haven't cras

Re: [ADMIN] persistent 'psql: FATAL: "listen_addresses" cannot be changed after server start

David Rovner wrote: >... > After some network configuration changes (IP changes and some editing to > config files which may have been interrupted buy shutdowns), I cannot > get past this error: That's a bit vague. What changed externally with your network? What files were you changing and what

Re: [ADMIN] PITR with rsync

> If you are going over an ssh connection then scp seems like the > appropriate tool. Yeah, rsync would work, but it's just a useless > extra layer of software... Actually, rsync has one edge over scp even where its other attributes are moot: atomicity. Rsync keeps the data in a temporary locati

Re: [ADMIN] Performance benchmarking

lai yoke hman wrote: > > Hello, > I am an undergraduate of a university in Malaysia, if I want to do > performance benchmark on PostgreSQL 8, do I need to get a license agreement > or some sort of thing? > Thanks. Your license, provided without charge, is here: http://www.postgresql.org/about/l

Re: [ADMIN] Function to offset current timestamp

Dan Harris wrote: > I'm trying to write a function that can do a select on an integer value > from a table and subtract that value from current_timestamp. > > Let's say I have a table called users and a field called tz_offset.. > > I want my function to do something like : > > select current_tim

Re: [ADMIN] two databases in one

Soulabaille Samantha wrote: > Hello, > > I'm working with a postgresql 7.4.7 > I've got two databases with the same tables and schema on two different > servers. > I want to have only one database. > So i would like to insert in the first database the data of the second > database. > I don't know

Re: [ADMIN] pg_dump from crontab

Nirav Parikh wrote: > Hi, > > I am having problem backing up database from crontab, I got following > lines in crontab file > > > 0 20 * * 5 /usr/pgsql/bin/pg_dump -F c database > > /usr/backup/friDATE4.pgdump > 0 20 * * 4 pg_dump -F c database > /usr/backup/thurDATE06-3.pgdump > > none of

Re: [ADMIN] Archive Command Configuration

Hi Steve, Thanks! for the quick reply, I thought about rsync too, but wasnt sure about completely how it handles partial files. I use rsync for all the backups, it works fine for all the application except for our mail application it copies the files but at the end of the job it gives me m

Re: [ADMIN] Archive Command Configuration

...I have a cron job from the production database that runs every 5-10min to check if there are any new archive logs and copy new archive logs to the remote stand by failover machine. The problem with this scenario is that there might be a possibility that I might scp a partially filled archi

Re: [ADMIN] Postgresql and uPortal

Given, Robert A wrote: Greetings, We are beginning to implement uPortal on Postgresql 7.4.8 on a z/Linux server. We are interested in obtaining information pertaining to any modification of the configuration parameters as distributed with the DBMS.. Start by taking a gander at: http:/

Re: [ADMIN] Semi OT - Anyone monitoring PostgreSQL with nagios

On Tuesday 08 November 2005 05:10, Lane Van Ingen wrote: > What does the 'c' part of the -tc command do? It is not documented > in any information I have. Show tuples only (per psql --help) Cheers, Steve ---(end of broadcast)--- TIP 2: Don't 'kill

Re: [ADMIN] dumping query results to a csv

On Thursday 25 August 2005 3:24 pm, David Durham wrote: > This is kind of a pg-admin newbie question, so apologies in > advance. > > Anyway, I'd like to issue a command that dumps the results of a > query to a txt file in comma delimited format. Does PostgreSQL > ship with something to do this? I

Re: [ADMIN] select * and save into a text file failed

On Friday 10 June 2005 10:03 am, Lee Wu wrote: > That is exactly what I did: > > \o a_lot_room_to_hold_my_result > select * from a_table > > either > 1. out of memory for query result > 2. killed > 3. crash PG > > "If you have a very large table you can exhaust memory on the > client side unless yo

Re: [ADMIN] select * and save into a text file failed

On Friday 10 June 2005 9:33 am, Lee Wu wrote: > Even without saving to file, it is still killed: >... > My_db=# select * from a_table; > Killed >... The previous examples don't work for me. In psql try this: --First set the output to a file \o 'my_output.txt' --Now run the query select * from myf

Re: [ADMIN] Database syncronization

On Wednesday 20 April 2005 9:44 am, Shadow wrote: > Hy all ! > > Is there any tool, middelware, or feature to syncronize 2 databases > (With the same schema, ofcourse) > > I have an application that runs from 2 points. One of this points > has a master database and the other point neds to sync its

Re: [ADMIN]

On Tuesday 19 April 2005 1:46 pm, Alvaro Herrera wrote: > Looks like you have some non-printable character in the used > column. I concur. Alternately you can (assuming you're running *nix which probably has xxd and more) try this to see exactly what is in the data: psql -c "select * from patie

Re: [ADMIN] Excessive growth of pg_attribute and other system tables

On Monday 21 March 2005 11:40 am, Tom Lane wrote: > "Matthew T. O'Connor" writes: > > I believe this discrepancy has to do with the fact that ANALYZE > > can return some very bogus values for reltuples, where as vacuum > > always returns an accurate count. I'm not sure how to best > > handle this

  1   2   >