Re: [GENERAL] unexplained autovacuum to prevent wraparound
Ah, now I see what you meant. Forgive me, I thought you were referring to the pg_autovacuum table in 8.3 where you have to specifiy something for each column, and -1 says use the default. It appears in 8.4.0 I have to explicitly set ALL (?) other storage parameters to -1 to get the default, otherwise I am getting zero for each value?? I don't believe the documentation mentions this rather important detail: http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS. Did I miss it somewhere? Thanks! Gordon On Fri, Mar 12, 2010 at 4:45 PM, Gordon Shannon wrote: > This is 8.4, there is no pg_autovacuum table. I set it like this: > > alter table foo set (autovacuum_analyze_scale_factor=0.01); > > > > > On Fri, Mar 12, 2010 at 4:31 PM, Alvaro Herrera < > alvhe...@commandprompt.com> wrote: > >> Gordon Shannon escribió: >> >> > One possibly interesting thing is that this seems to have started just >> after >> > I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more >> > frequent analyze runs. I wonder if that could be related. >> >> You probably set the other values to 0, which includes the freeze age. >> You need to set it (and all other values) to -1 instead. >> >> -- >> Alvaro Herrera >> http://www.CommandPrompt.com/ >> PostgreSQL Replication, Consulting, Custom Development, 24x7 support >> > > > -- If I had more time, I could have written you a shorter letter. (Blaise Pascal)
[GENERAL] libpq: compatibility with server versions
Hi all, I am using libpq 8.2.4 (and my own wrapper around it) for a long time now. Due to some performance penalties I would like to upgrade to 8.4.x libpq. Is it o.k. if I upgraded my libpq to the newer 8.4 libraries but would still connect to old 8.2 servers? Are there any compatibility issues to be aware of? (I am not talking about upgrading the server nor the steps to take for upgrading the data itself.) Thank You Felix -- 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: Package compat-postgresql-libs-4-1PGDG.rhel4.i386.rpm is not signed
Padmanabhan G wrote: Hi, I have an error while installing Postsgresql 8.3 in Cent OS 4. I did the following... # rpm -ivh pgdg-centos-8.3-6.noarch.rpm # #vi /etc/yum.repos.d/CentOS-Base.repo Add "exclude=postgresql*" to both section [base] and [update]. #yum install postgresql-server Dependencies Resolved = Package Arch Version Repository Size = Installing: postgresql-server i386 8.3.9-1PGDG.rhel4 pgdg834.4 M Installing for dependencies: compat-postgresql-libs i386 4-1PGDG.rhel4 pgdg83 56 k postgresql i386 8.3.9-1PGDG.rhel4 pgdg831.7 M Updating for dependencies: perl-DBD-Pg i386 1.49-2.el4s1 centosplus 112 k postgresql-libs i386 8.3.9-1PGDG.rhel4 pgdg83190 k postgresql-odbc i386 08.03.0400-1PGDG.rhel4 pgdg83229 k postgresql-python i386 8.1.15-1.el4s1.1 centosplus 63 k postgresql-tcl i386 8.1.15-1.el4s1.1 centosplus 79 k Transaction Summary = Install 3 Package(s) Update 5 Package(s) Remove 0 Package(s) Total download size: 6.9 M Is this ok [y/N]: y Downloading Packages: Package compat-postgresql-libs-4-1PGDG.rhel4.i386.rpm is not signed Best Regards, G. Padmanabhan -- என்றும் அன்புடன் உங்கள் பத்மநாபன் Hello, This package should be fixed. Sorry about the trouble. Please run yum update or download the latest pgdg repository rpm and install it, as it contains a new signing key. http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-centos-8.3-7.noarch.rpm This should correct that error. Please let us know if it does not. Regards, Lacey -- Lacey Powers The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 104 PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] restoring a database to its initial state
Pavel Stehule writes: > 2010/3/13 Manlio Perillo : >> Usually when I need to restore a database to its initial state, what I >> do is to simply drop it, and then re-create it. >> However on a shared hosting this is not possible. > Usually people use a install and a uninstall scripts. First creates > dbobjects, second removes objects. "pg_dump --clean" can help with creating an uninstall script. regards, tom lane -- 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] restoring a database to its initial state
Hyho! On Saturday 13 March 2010 13.48:14 Manlio Perillo wrote: > Usually when I need to restore a database to its initial state, what I > do is to simply drop it, and then re-create it. > > However on a shared hosting this is not possible. Create a schema, modify your default search path so that you're always working in your new schema, then you can just drop schema foo cascade; cheers -- vbi -- Today is Boomtime, the 72nd day of Chaos in the YOLD 3176 signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] restoring a database to its initial state
Hello 2010/3/13 Manlio Perillo : > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hi. > > Usually when I need to restore a database to its initial state, what I > do is to simply drop it, and then re-create it. > > However on a shared hosting this is not possible. > > By initial state I mean: > The content of template1 database > or > The database without all objects owned by role X, and that can be > re-created by X > > where X is a role with normal privileges. > > > I searched on pgFoundry, without success. > > It should not be hard to implement, but I would like to know if > something similar has already been implemented. > > Usually people use a install and a uninstall scripts. First creates dbobjects, second removes objects. Regards Pavel Stehule > > Thanks Manlio > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iEYEARECAAYFAkubiY4ACgkQscQJ24LbaURhBgCdHHSop6sxE1iGhGScLgQxTs0y > F54An3uYbIA5NqXIb79n1IQZA4cKb7XQ > =sooF > -END PGP SIGNATURE- > > -- > 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
[GENERAL] restoring a database to its initial state
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. Usually when I need to restore a database to its initial state, what I do is to simply drop it, and then re-create it. However on a shared hosting this is not possible. By initial state I mean: The content of template1 database or The database without all objects owned by role X, and that can be re-created by X where X is a role with normal privileges. I searched on pgFoundry, without success. It should not be hard to implement, but I would like to know if something similar has already been implemented. Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkubiY4ACgkQscQJ24LbaURhBgCdHHSop6sxE1iGhGScLgQxTs0y F54An3uYbIA5NqXIb79n1IQZA4cKb7XQ =sooF -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Hints for postgresql package recompilation (FIXED)
FIXED!! compilation needs a non root user (initdb complains about root during tests )... regards, Angel # Hi, We are trying to deploy an OpenBravo installation on OpenSuSE 11.2 X64 and are using Postgresql 8.4.2-1.1.1 as the data backend, but OpenSuSE rpm seems to lack uuid-ossp bindings, so we tried to regenerate from src rpm. After getting the libuuid ossp sources, we used the --with-uuid-ossp switch on a X86 laptop as a test before the final compilation on X64 and tried to rebuilt the package several times but we are hitting a regresion error (build.log attached). Anyone can provide usefull hints about this issue? We need to check this out before trying to contact opensuse packagers... Thanks! PS: package used is : http://download.opensuse.org/update/11.2/rpm/src/postgresql-8.4.2-1.1.1.src.rpm /Angel Most people know C is not so high level ...Everybody else just got assembler overdose -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] explicit cast for null::bigint
I've found this a bit strange: insert into catalog_related (itemid, related_itemid, rank) select distinct itemid, null::bigint, 0::float4 from catalog_categoryitem where catid is null or catid<>0 and itemid not in (select itemid from catalog_related); create table catalog_related ( itemid bigint, updated timestamp default now(), related_itemid bigint, rank float4 ); without the explicit cast for the null I get ERROR: column "related_itemid" is of type bigint but expression is of type text LINE 1: insert into catalog_related (itemid, related_itemid, rank) ^ HINT: You will need to rewrite or cast the expression. PostgreSQL 8.3.4 on x86_64-pc-linux-gnu -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] hardware for a server
On Sat, Mar 13, 2010 at 1:34 AM, A B wrote: > Hello. > > It's time to get new hardware for a server that will run both > PostgreSQL and Apache. > The workload will be similar to that of your standard "PHP forum" > (most selects and logging of stuff that has been read) > > The modell I'm looking at right now is > > 2x Xeon E5520 2,26 GHz 8 MB (8 cores in total) > 24 GB 1066 MHz DDR 3 ECC (or more) > > When it comes to a RAID controller I have the choice of: > > 3Ware SAS 9690SA-8i 512 MB BBU > Adaptec SAS Raid 5805 256 MB BBU > LSI MegaRaid SAS 8708 128 MB BBU > > Any advice/experience on what raid controller to pick? The 3ware has > the most memory and I've read some good reviews on that one. > The OS will be CentOS 5.4 I can't comment on any of those particular controllers. There's a benchmark here: http://www.tomshardware.com/reviews/adaptec-serial-controllers,1806-11.html > When it comes to harddrives I think my best optins is to use > > * 4 discs (raid 10) for the database > > and > > * 4 discs (raid 10) for OS, xlog and other data (images,avatars etc.) > or more likely: > * 2 discs (raid 1) for OS/xlog and > * 2 discs (raid1) for "other data" Leaving out 2 drives for other data is kind of a waste of spindles. You can network mount terabytes from a file server full of SATA drives for much less. Also... > When it comes to choosing the acctual discs I guess this would be > appropriate to use: > "other data": Barracda ES.2 1000 GB (SATA) to get a a good GB/$ ratio. > OS/xlog : Barracuda ES.2 500 GB (SAS) > DB: Cheeta 15K.6 146 GB (SAS) (The 300 GB would be better if I can > find some more money) Mixing SATA and SAS drives on the same controller can be problematic. Some controllers don't behave well when you mix and match. I'd suggest building an 8 disk RAID-10 and a single mirror + 6 disk RAID-10 and testing both configurations. If you need more storage look at 300G SAS drives. Your two bottleneck are likely to be IO random write ops and / or CPU horsepower, depending on how your web app is built. You can always buy another $1500 box with hot cpus and a pair of big SATA drives if you need more CPU horsepower, but beefing up IO is a lot hard once your db server is in place. > This of course gives me a headache when it comes to keeping spare discs. > > The other option would be to use OS/xlog and DB on Barracuda ES.2 500 > GB (SAS). I have no idea what that mean to the performance. A lot/ > barely noticable? The cost diff now on 500 and 1TB drives is too low to bother with 500 and 1TB mixed, just get 1TB. And I can't really recommend Seagate ES.2 or 7200.11 drives right now with the failure rates I've been seeing. > Any comments, advice on this kind of setup? If you've got a lot of reads going on be sure to toss memcached into this equation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] hardware for a server
Hello. It's time to get new hardware for a server that will run both PostgreSQL and Apache. The workload will be similar to that of your standard "PHP forum" (most selects and logging of stuff that has been read) The modell I'm looking at right now is 2x Xeon E5520 2,26 GHz 8 MB (8 cores in total) 24 GB 1066 MHz DDR 3 ECC (or more) When it comes to a RAID controller I have the choice of: 3Ware SAS 9690SA-8i 512 MB BBU Adaptec SAS Raid 5805 256 MB BBU LSI MegaRaid SAS 8708 128 MB BBU Any advice/experience on what raid controller to pick? The 3ware has the most memory and I've read some good reviews on that one. The OS will be CentOS 5.4 When it comes to harddrives I think my best optins is to use * 4 discs (raid 10) for the database and * 4 discs (raid 10) for OS, xlog and other data (images,avatars etc.) or more likely: * 2 discs (raid 1) for OS/xlog and * 2 discs (raid1) for "other data" When it comes to choosing the acctual discs I guess this would be appropriate to use: "other data": Barracda ES.2 1000 GB (SATA) to get a a good GB/$ ratio. OS/xlog : Barracuda ES.2 500 GB (SAS) DB: Cheeta 15K.6 146 GB (SAS) (The 300 GB would be better if I can find some more money) This of course gives me a headache when it comes to keeping spare discs. The other option would be to use OS/xlog and DB on Barracuda ES.2 500 GB (SAS). I have no idea what that mean to the performance. A lot/ barely noticable? Any comments, advice on this kind of setup? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
