[ADMIN] Create Index Performance Issue
Hi, I'm currently testing a 25G Postgres 8.3.0 database on Apple Xserve/ Intel with Mac OSX 10.5 (Leopard). There are two identical machines with identical configurations (hw and sw). Running a pg_restore of a binary backup file (3.8 GB) on both machines gives the following results: Machine A : 90 minutes Machine B: 60 minutes The postgres server logfiles show the cause of the time difference: The SQL query CREATE INDEX login_session_identifier ON login_session USING btree (identifier) consumes very different execution times: Machine A : 1905 seconds Machine B: 292 seconds The difference of 27 minutes explains the pg_restore behaviour. The same effect can be demonstrated easily by just running the SQL query manually. Some database infos: Table login_session : 33,996,225 rows, 10.6 GB on disk Column identifier : character varying (255) Index login_session_identifier : 1.3 GB on disk Postgres configuration file is of course the same on both machines. The maintenance_work_mem is set to 128MB . I played with this parameter setting it up to 2 GB. This didn't change the execution times essentially. With large values the times went a little up. I would appreciate any advices on how to investigate this problem any further. Ideas are very welcome. Thanks in advance, Rudolf VanderLeeden Logic United GmbH, Germany [EMAIL PROTECTED] -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Create Index Performance Issue
On Wed, Jun 04, 2008 at 11:43:24AM +0200, Rudolf van der Leeden wrote: > I'm currently testing a 25G Postgres 8.3.0 database on Apple Xserve/ > Intel with Mac OSX 10.5 (Leopard). > > There are two identical machines with identical configurations (hw > and sw). > Running a pg_restore of a binary backup file (3.8 GB) on both > machines gives the following results: > > Machine A : 90 minutes > Machine B: 60 minutes Maybe there are some background processes running? Tino. -- "What we resist, persists." (Zen saying) www.craniosacralzentrum.de www.forteego.de -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Corrupted WAL during recovery from archived log
Hi, I'm writing some paper about instance recovery and I'm wondering how PostgreSQL act when find a corrupted archived WAL during the restore command. I found that when instance recovery starts the zero_damaged_pages is set to true. This parameter affect only data files with corrupted pages or even wal pages that are zeroed before going on data file? Thanks in advance Kind regards Federico -- (all opinions expressed are my own) Federico Campoli PostgreSQL Consulting -> PGHost http://www.pghost.eu -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Is it possible to convert WAL's between architectures?
As in the title - is it possible to convert WAL for another architecture? (source database on Linux/i386, contemplating chances to restore backup on Linux/amd64) -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] unrecognized configuration parameter "synchronize_seqscans"
"Mikel Lindsaar" <[EMAIL PROTECTED]> writes: > I am trying to set up a pg_dump between a postgresql 8.3.1 server and edb > 8.3 server. > When I do: > pg_dump -s -U testmaster -h 10.0.0.1 -p 5444 testuser | psql -U postgres -h > localhost testslave > I get: > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: unrecognized configuration > parameter "synchronize_seqscans" > pg_dump: The command was: SET synchronize_seqscans TO off Hmm, apparently the source isn't *really* 8.3 ... but it must claim to be, else pg_dump wouldn't try to send it that command. I think you're confused about the version situation, and you've probably also got some messy combination of stock PG server and edb-customized pg_dump, or perhaps vice versa. You're going to have to go ask them for help if you can't get it sorted by triple-checking the versions. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Create Index Performance Issue
Rudolf van der Leeden <[EMAIL PROTECTED]> writes: > [ different times to build a varchar index on allegedly identical > installations ] Perhaps one is using C locale and the other is not? strcmp() vs strcoll() is a pretty big hit. In general it'd be worth dumping out the whole contents of pg_settings and diffing those two files to see if you missed any configuration differences. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Postgres security issue - protecting datafiles
Hi, I'd like to use postgres to store my secret data in a way that only me (the DBA, owner of the table) can access the talbe while the root (system administrator) who installed and maintains the server - will not be able to see the data. Now, I think that "db authentication" is simply not enough , because the administrator can copy the data files to his own machine (where he is the owner of the database). Is there a way to protect the data files, so even the "malicious administrator" cannot see the data ? tnx, zferentz -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgres security issue - protecting datafiles
On Mon, Jun 2, 2008 at 6:16 PM, <[EMAIL PROTECTED]> wrote: > Is there a way to protect the data files, so even the "malicious > administrator" cannot see the data ? Encrypt it. Though, how you store the key will be important. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgres security issue - protecting datafiles
On Mon, Jun 02, 2008 at 03:16:54PM -0700, [EMAIL PROTECTED] wrote: > I'd like to use postgres to store my secret data in a way that only > me (the DBA, owner of the table) can access the talbe while the root > (system administrator) who installed and maintains the server - will > not be able to see the data. > > Now, I think that "db authentication" is simply not enough , because > the administrator can copy the data files to his own machine (where he > is the owner of the database). > > Is there a way to protect the data files, so even the "malicious > administrator" cannot see the data ? I suppose, you need to look into SELinux. That might give you the infrastructure for such purposes, together with an encrypted file system for DB space. That's not something you can do via an application. As you already noticed, anybody with access to the physical table data will be able to copy it. Of course, you could encrypt the data stored in the table, but then: where's the key for decryption? Tino. -- "What we resist, persists." (Zen saying) www.craniosacralzentrum.de www.forteego.de -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgres security issue - protecting datafiles
> Now, I think that "db authentication" is simply not enough , because > the administrator can copy the data files to his own machine (where he > is the owner of the database). Or just change pg_hba.conf to his taste > Is there a way to protect the data files, so even the "malicious > administrator" cannot see the data ? Encrypt it. And keep the key on some other machine. And plan your application so decryption happens on some other machine -- -- | Marcin Kasperski | Communication takes place between people, | http://mekk.waw.pl | documents are secondary. (Booch) || -- -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgres security issue - protecting datafiles
[EMAIL PROTECTED] writes: > I'd like to use postgres to store my secret data in a way that only > me (the DBA, owner of the table) can access the talbe while the root > (system administrator) who installed and maintains the server - will > not be able to see the data. If you think you can hide anything on a machine from its root admin, I have a bridge I'd like to sell you ... The only thing you could do along this line is to use the database as a dumb container for encrypted data that you decrypt only on the client side, using keys that are never sent to the database server. Unfortunately that approach loses most of the benefit of using a database in the first place, since you can hardly do any useful processing on data that you can't decrypt. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] tsearch2 dictionary for statute cites
Has anyone dealt with the issue of using tsearch2 with statute cites yet? Do you have a sample dictionary or any tips? We need to confirm with the users, but I think desired behavior is that a statute cite of '813.12(1)(am)' should be found on any leading portions -- that is, any of the following search arguments should find it: 813 813.12 813.12(1) 813.12(1)(am) On the other hand, a document containing '813.12(1)' (without the '(am)' should be found by the first three of the above, but not the last. This would seem to require using a slightly different dictionary set to create the tsquery than to create the tsvector. There's no problem with that, is there? -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] tsearch2 dictionary for statute cites
Kevin, in 8.4 it'd be possible to use prefix search, see our presentation about GIN at PGCon 2008. For now you can use dict_regexp, which allows to use perl's regular expressions. Get it from http://vo.astronet.ru/arxiv/dict_regex.html Oleg On Wed, 4 Jun 2008, Kevin Grittner wrote: Has anyone dealt with the issue of using tsearch2 with statute cites yet? Do you have a sample dictionary or any tips? We need to confirm with the users, but I think desired behavior is that a statute cite of '813.12(1)(am)' should be found on any leading portions -- that is, any of the following search arguments should find it: 813 813.12 813.12(1) 813.12(1)(am) On the other hand, a document containing '813.12(1)' (without the '(am)' should be found by the first three of the above, but not the last. This would seem to require using a slightly different dictionary set to create the tsquery than to create the tsvector. There's no problem with that, is there? -Kevin Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] FATAL Authentication
I am trying to run commands from the shell (without becoming the postgres user first), which work fine on our live server, but on our dev server I receive FATAL authentication errors. [EMAIL PROTECTED] tmp]$ createdb -U postgres lan_portal createdb: could not connect to database postgres: FATAL: Ident authentication failed for user "postgres" [EMAIL PROTECTED] tmp]$ psql -U postgres lan_portal < /tmp/lan_portal.bck psql: FATAL: Ident authentication failed for user "postgres" What needs to be configured so I can run the above commands in the shell? I was able to create the database once I "su" to postgres, using CREATE DATABASE, but I do not know how to restore a database as I do in the shell as listed above. Thanks Marc -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] FATAL Authentication
Marc Fromm <[EMAIL PROTECTED]> writes: > I am trying to run commands from the shell (without becoming the postgres > user first), which work fine on our live server, but on our dev server I > receive FATAL authentication errors. > [EMAIL PROTECTED] tmp]$ createdb -U postgres lan_portal > createdb: could not connect to database postgres: FATAL: Ident > authentication failed for user "postgres" The -U option is just about entirely useless under "ident sameuser" authentication, which apparently is what you are using. If you like ident auth in general, you can make it do what you want by setting up a map file that lets (eg) root become any of a list of postgres user names. Or you could switch to some other auth method. See http://www.postgresql.org/docs/8.3/static/client-authentication.html regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
