Re: [ADMIN] Backing up postgresql databases
Dixit Tim Frank <[EMAIL PROTECTED]> (le Tue, 20 Mar 2001 00:14:11 GMT) : » Have your shell script do » » export PGUSER=username » export PGPASSWORD=password » » before you run pg_dumpall in the same script. The user/pass would most » likely have to be a superuser to have access to all databases (this is » also not guaranteed depending on your pg_hba.conf). Make the script » read/execute by root but not by anyone else and it will help a tiny bit » with security. Using something like "ps -e" shows the environment variables so it is as unsecure as giving the password on the commande line. Thierry ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[ADMIN] Re: Re: cannot create new user in postgres
[EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >I am trying to create the user but it was giving folllowing errors > >[postgres@ns data]$ su siddharta >su: user siddharta does not exist Meaning: there is no Unix user 'siddharta' on your system. >This is how I created the user [createuser] Meaning: there is now a PostgreSQL database user 'siddharta'. PostgreSQL uses a user system that is separate from the underlying operating system. There is no problem, just a misunderstanding on your part. You can now use the database user you created with PostgreSQL's commands, e.g "psql -U siddharta". HTH, Ray -- "If we put in English phrases, that makes it readable". That's COBOL. Larry Wall on common fallacies of language design ---(end of broadcast)--- TIP 3: 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: [ADMIN] Backing up postgresql databases
Title: RE: [ADMIN] Backing up postgresql databases Is there any reason why programs like this could not be given a simple properties file which contains the username and password. This file could then be passed on the command line, but nobody (other than, say, root, or postgres) would have access to it at all. I've seen a number of systems use this type of solution, and although it appears superficially useless (am I opening myself to be shot down or what ;-), the security of the file system creates (as far as I can see) reasonable safety. Just my €25... MikeA >> -Original Message- >> From: Thierry Besancon [mailto:[EMAIL PROTECTED]] >> Sent: 20 March 2001 08:34 >> To: Tim Frank >> Cc: [EMAIL PROTECTED] >> Subject: Re: [ADMIN] Backing up postgresql databases >> >> >> Dixit Tim Frank <[EMAIL PROTECTED]> (le Tue, 20 >> Mar 2001 00:14:11 GMT) : >> >> » Have your shell script do >> » >> » export PGUSER=username >> » export PGPASSWORD=password >> » >> » before you run pg_dumpall in the same script. The >> user/pass would most >> » likely have to be a superuser to have access to all >> databases (this is >> » also not guaranteed depending on your pg_hba.conf). Make >> the script >> » read/execute by root but not by anyone else and it will >> help a tiny bit >> » with security. >> >> Using something like "ps -e" shows the environment variables so it is >> as unsecure as giving the password on the commande line. >> >> Thierry >> >> ---(end of >> broadcast)--- >> TIP 6: Have you searched our list archives? >> >> http://www.postgresql.org/search.mpl >> _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
[ADMIN] Re: PostgreSQL; Strange error
Bengt Månsson <[EMAIL PROTECTED]> wrote: >I use RH7, Apache, PHP4 and PostgreSQL Your problem is a RDBMS problem, not a WWW server one. The PostgreSQL mailing lists (see http://www.postgresql.org/users-lounge/index.html) are a better place to ask. >Warning: PostgreSQL query failed: FATAL 1: my bits moved right off the end >of the world! Recreate index pg_attribute_relid_attnum_index. >I don't understand any of this. Never heard of that index. It starts with "pg_", so it's Postgres' meta-data (internal administration). >What does it mean? Apparently one of Postgres' internal indexes got corrupted. > What can I do? Follow the suggestion given in the warning: recreate index pg_attribute_relid_attnum_index, presumably [*] using something like DROP INDEX pg_attribute_relid_attnum_index; CREATE UNIQUE INDEX pg_attribute_relid_attnum_index ON pg_attribute(attrelid,attnum); (as database user 'postgres'). HTH, Ray [*] I'm just guessing from '\d pg_attribute_relid_attnum_index' and '\d pg_attribute' - back up your database (using "pg_dump") before trying this. -- Think of computer security like powertools. The day you think you are totally safe is the day you end up hurt. Alan Cox ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Re: PostgreSQL; Strange error
[EMAIL PROTECTED] (J.H.M. Dassen (Ray)) writes: > Bengt Månsson <[EMAIL PROTECTED]> wrote: >> Warning: PostgreSQL query failed: FATAL 1: my bits moved right off the end >> of the world! Recreate index pg_attribute_relid_attnum_index. This is an internal "can't happen" failure condition, presumably arising from some weird corner-case bug in btree index manipulation. We have seen sporadic reports of this failure, mostly from people using lots of large objects, but no one has yet provided a test case that allows the problem to be reproduced from scratch. When I'm in an optimistic mood I think that the bug might be fixed in 7.1, but it's hard to say for sure because I've never seen this failure happen myself. > Follow the suggestion given in the warning: recreate index > pg_attribute_relid_attnum_index, presumably [*] using something like > DROP INDEX pg_attribute_relid_attnum_index; > CREATE UNIQUE INDEX pg_attribute_relid_attnum_index ON > pg_attribute(attrelid,attnum); I think that will probably crash and burn :-(. To reconstruct a broken system index you need to use the REINDEX command. See the manual for the proper procedure for using REINDEX on system indexes; it's a tad arcane. regards, tom lane ---(end of broadcast)--- TIP 3: 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: [ADMIN] Backing up postgresql databases
Thierry, I think you meant to say "ps -e e" (at least that is what it is on my RedHat Linux servers). I also realise the security issue with this. However, if you really HAVE to use password authentication and you really HAVE to dump your data then you are already painted into a corner so to speak. I apologies for not mentioning the security issue in my post. The reason the "export" is needed for pg_dumpall is because it is not very password friendly and calls a combination of psql and pg_dump to do it's job. I recall when looking at the pg_dumpall script that it doesn't actually pass a username/password to psql, or if you do specify the -U then you get an "unknown option" error when it tries to run pg_dump. Tim Frank >> Original Message << On 20/03/01, 3:33:52 AM, Thierry Besancon <[EMAIL PROTECTED]> wrote regarding Re: [ADMIN] Backing up postgresql databases: > Dixit Tim Frank <[EMAIL PROTECTED]> (le Tue, 20 Mar 2001 00:14:11 GMT) : > » Have your shell script do > » > » export PGUSER=username > » export PGPASSWORD=password > » > » before you run pg_dumpall in the same script. The user/pass would most > » likely have to be a superuser to have access to all databases (this is > » also not guaranteed depending on your pg_hba.conf). Make the script > » read/execute by root but not by anyone else and it will help a tiny bit > » with security. > Using something like "ps -e" shows the environment variables so it is > as unsecure as giving the password on the commande line. > Thierry ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [ADMIN] Backing up postgresql databases
Mike, From a different perspective I toyed around briefly with the idea of creating a user called "backup" that would merely have SELECT permissions on all tables in all databases in order to perform a pg_dump or pg_dumpall. This works fine for a pg_dump as it does a single database at a time. The problem was that I couldn't figure out a way to automatically set SELECT permissions for the backup user when a new table was created in the database. You can't set a default value in the pg_class table nor create a trigger on insert as it complains about "can't modify a system catalogue". Maybe there is some other way to get around that, and I would be more than happy to hear comments on this. The other problem I found was that when using pg_dumpall it dumps all the database usernames/passwords so the "backup" user would need select permissions on pg_shadow which contains all of the usernames/passwords. Well, I kinda quit right there as far as using this restricted "backup" user for pg_dumpall because if it can select all users/passwords from the database then storing this combination in a shell script/environment variable isn't any more secure. Sure, it takes one more step to get ALL usernames/passwords, but that doesn't seem to be worth the effort. Thinking about it now and seeing a shell script somebody posted a little while ago to do a vacuum and pg_dump it might not be such a bad idea to go back to the "backup" user with SELECT only permissions on the tables. Just not sure how to set the permissions on newly created tables by default, maybe it just has to be manually. I would greatly appreciate comments on this idea and if it is worth anything. I've teetered back and forth for some time. Tim Frank Original Message dated 20/03/01, 6:48:08 AM Author: Michael Ansley <[EMAIL PROTECTED]> Re: RE: [ADMIN] Backing up postgresql databases: Is there any reason why programs like this could not be given a simple properties file which contains the username and password. This file could then be passed on the command line, but nobody (other than, say, root, or postgres) would have access to it at all. I've seen a number of systems use this type of solution, and although it appears superficially useless (am I opening myself to be shot down or what ;-), the security of the file system creates (as far as I can see) reasonable safety. Just my ¬25... MikeA >> -Original Message- >> From: Thierry Besancon [mailto:[EMAIL PROTECTED]] >> Sent: 20 March 2001 08:34 >> To: Tim Frank >> Cc: [EMAIL PROTECTED] >> Subject: Re: [ADMIN] Backing up postgresql databases >> >> >> Dixit Tim Frank <[EMAIL PROTECTED]> (le Tue, 20 >> Mar 2001 00:14:11 GMT) : >> >> »Have your shell script do >> » >> » export PGUSER=username >> » export PGPASSWORD=password >> » >> » before you run pg_dumpall in the same script. The >> user/pass would most >> » likely have to be a superuser to have access to all >> databases (this is >> » also not guaranteed depending on your pg_hba.conf). Make >> the script >> » read/execute by root but not by anyone else and it will >> help a tiny bit >> » with security. >> >> Using something like "ps -e" shows the environment variables so it is >> as unsecure as giving the password on the commande line. >> >> Thierry >> >> ---(end of >> broadcast)--- >> TIP 6: Have you searched our list archives? >> >> http://www.postgresql.org/search.mpl >> _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __ ---(end of broadcast)--- TIP 3: 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: [ADMIN] Re: PostgreSQL; Strange error
"Mikheev, Vadim" <[EMAIL PROTECTED]> writes: >> Warning: PostgreSQL query failed: FATAL 1: my bits moved >> right off the end of the world! Recreate index >> pg_attribute_relid_attnum_index. > Just use gdb to prevent parent btree page update after > split and you'll get that error next time when splitting > new, unpointed from parent, right sibling. Hmm ... so you think the people who have complained of this are all working with databases that have suffered previous crash corruption? I doubt it. There's too much consistency to the reports: in particular, it's generally triggered by creation of lots of large objects, and it's always the indexes on pg_attribute, never any other table (even though large object creation inserts into several system tables). I don't see how the unfinished-split hypothesis explains that. My thought was that it is somehow related to the many-equal-keys issues that we had in 7.0.* and before, and/or the poor behavior for purely sequential key insertion that we still have. But without a test case it's hard to be sure. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] RE: [ADMIN] Re: PostgreSQL; Strange error
> Hmm ... so you think the people who have complained of this are all > working with databases that have suffered previous crash corruption? > I doubt it. There's too much consistency to the reports: in > particular, it's generally triggered by creation of lots of large > objects, and it's always the indexes on pg_attribute, > never any other table (even though large object creation inserts into > several system tables). I don't see how the unfinished-split hypothesis > explains that. I saw this error after PG' crashes and power off in my employer' project where large objects were not used. As for pg_attributes - PG inserts into this table more rows than into others => more splits => higher probability of unfinished split in the event of crash. > My thought was that it is somehow related to the many-equal-keys issues > that we had in 7.0.* and before, and/or the poor behavior for purely pg_attribute_relid_attnum_index is unique index, so I doubt that "many-equal-keys issue" is related to subj. > sequential key insertion that we still have. But without a test case > it's hard to be sure. This is hypothesis and we don't know how to test it. But unfinished splits is not hypothesis. It's *obviously* may cause "my bits moved right off the end of the world" error and we can test this very easy. Vadim ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] I cannot vacuum
Hi, I have a problem today when I vacuum one table which is vacuumed every today, I tried re_create index also, didn't work. urldb=# VACUUM VERBOSE ANALYZE id; NOTICE: Vacuum: table not found VACUUM urldb=# VACUUM VERBOSE ANALYZE ip; NOTICE: --Relation ip-- NOTICE: Pages 4373: Changed 0, reaped 151, Empty 0, New 0; Tup 662929: Vac 164, Keep/VTL 0/0, Crash 0, UnUsed 23401, MinLen 48, MaxLen 48; Re-using: Free/Avail. Space 1137624/0; EndEmpty/Avail. Pages 150/0. CPU 0.33s/2.28u sec. NOTICE: Index ip_ip: Pages 2093; Tuples 662929: Deleted 0. CPU 0.15s/0.73u sec. NOTICE: Index ip_id: Pages 1311; Tuples 662929: Deleted 0. CPU 0.11s/0.70u sec. NOTICE: Rel ip: Pages: 4373 --> 4223. NOTICE: FlushRelationBuffers(ip, 4223): block 4365 is referenced (private 0, global 8) FATAL 1: VACUUM (vc_vacheap): FlushRelationBuffers returned -2 pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. any suggestion?? Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [ADMIN] Re: PostgreSQL; Strange error
> >> Warning: PostgreSQL query failed: FATAL 1: my bits moved > >> right off the end of the world! Recreate index > >> pg_attribute_relid_attnum_index. > > This is an internal "can't happen" failure condition, > presumably arising from some weird corner-case bug in btree > index manipulation. We have seen sporadic reports of this > failure, mostly from people using lots of large objects, > but no one has yet provided a test case that allows > the problem to be reproduced from scratch. When I'm in an Just use gdb to prevent parent btree page update after split and you'll get that error next time when splitting new, unpointed from parent, right sibling. In real life you have !0 probability to get same case without gdb just by pg_ctl -m fast stop, not to mention pg_ctl -m immediate stop, power off and OS' crash. > optimistic mood I think that the bug might be fixed in 7.1, > but it's hard to say for sure because I've never seen this > failure happen myself. Just add elog(ERROR) after split to see this. This is what I made to test new btree runtime recovery code in 7.1. Vadim ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] auto-starting postmaster with -i option
Greetings. Running 7.0.3 under Debian 2.2, I'd like postmaster to start on boot with the -i option. I've got it working with just the defaults, using start-stop-daemon, but for some reason it ignores the section after the --, which the start-stop-daemon documentation claims will be passed as arguments to the executable. Here's the command from the /etc/init.d/postgres file: start-stop-daemon --chuid postgres --exec /usr/local/pgsql/bin/postmaste r --start /usr/local/pgsql/bin/postmaster -- -S -d -i -D/var/lib/postgres/data But here's the output of pg_ctl status: postgres@nujoma:~$ pg_ctl status pg_ctl: postmaster is running (pid: 11145) options are: /usr/local/pgsql/bin/postmaster -p 5432 -D /var/lib/postgres/data -B 64 -b /usr/local/pgsql/bin/postgres -N 32 -S If a kind soul who has this working would be willing to send me some tips I'd appreciate it. ap -- Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology (Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill) [EMAIL PROTECTED] - http://www.unc.edu/~aperrin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Re: [SQL] I cannot vacuum
Jie Liang <[EMAIL PROTECTED]> writes: > NOTICE: FlushRelationBuffers(ip, 4223): block 4365 is referenced (private > 0, global 8) > FATAL 1: VACUUM (vc_vacheap): FlushRelationBuffers returned -2 Try restarting your postmaster. regards, tom lane ---(end of broadcast)--- TIP 3: 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
