[ADMIN] After reinstalling postgres...
Hi all, Some days ago I need to reinstall postgresql 6.3.2. Before doing this I made a backup of my databases just copying the files in /var/lib/pgsql. Did I make the backup correctly? After that, I install postgres (6.3.2). Then, I copied my databases into the directory /var/lib/pgsql/base. When I was trying to connect to one of my databases, I received a message like this: " does not exist in pg_database". So, I copied the old pg_database to /var/lib/pgsql. But it doesn't work. Now it's reporting another error. After explaining the problem, I hope someone helps me, by saying how can I use my databases into the new installation of postgres. Thanks, Luiz
[ADMIN] Why do I receive this message?
Hi all, I'm a little bit curious with a message I've been receiveing all times I connect to a database. When I run "psql chs" I receive this message. >Welcome to the POSTGRESQL interactive sql monitor: > Please read the file COPYRIGHT for copyright terms of POSTGRESQL > > type \? for help on slash commands > type \q to quit > type \g or terminate with semicolon to execute query > You are currently connected to the database: chs > >chs=> I'm curious with the follow line: You are currently connected to the database: chs This database is used with a CGI and it's accessed by Netscape Navigator and I don't have any other conection opened except this one. Why am I receiving this message? Thanks in advance for any help --- Luiz Laurino Rio Grande - Brazil
[ADMIN] Crypt and DELETE.
Hy all, I'm using the delete command with "PQexec" to remove some records from my database. But there is a problem, how can I know if the record was really removed if the values returned in "PQresultStatus" are the same when no records and one record are removed. Should I use a special function to make queries like INSERT, DELETE or UPDATE ? I have another doubt about "crypt", I need crypt and descrypt some data but I don't know the sintax of the command, could someone help me? Tanks a lot, Rodrigo.
[ADMIN] FATAL 1
I get the "FATAL 1" message from "postgres -i": [postgres@lexis postgres]$ postgres -i FATAL 1: Database postgres does not exist in pg_database FATAL 1: Database postgres does not exist in pg_database [postgres@lexis postgres]$ What may I be doing wrong? (By the way, where is the "-i" option documented? The HTML manual I am following tells you to use it but does not explain it, neither does the man page.) My settings: PATH=$PATH:/usr/bin MANPATH=$MANPATH:/usr/man PGLIB=/usr/lib/pgsql PGDATA=/home/postgres/data "initdb" seemed to run ok. Thanks a lot.
[ADMIN] Switch log (WAL)
Hello, I'm new to the list, and I'm intereted in PostgreSQL replication (using WAL) As I didn't find any script to do that, I make mine, that seems to work. However, I wonder how can I "switch WAL logs", in order to have the standby database not too much late. The database actually generates few logs, and I would like one WAL to be generated once per hour. How Can I do that ? Regards, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Switch log (WAL)
Thanks...
Mhh too bad. So, that's what I'll do. I'm actually working on a script in
order to create a standby database, without even logging to it, and I would
like to avoid such a resident program on the master database... So, I'll
probably do smth with cron.
Here is my actual script :
#!/bin/sh
#
# This script will create a hot standby database replicated of your PostgreSQL
# database.
# This has been tested on PostgreSQL 8.1
#
# In order to work, this script assume that :
# - postgresql is installed on the standby database host.
# - The PGDATA is at the same place on both primary and standby database.
# - You made an ssh-keygen -t dsa on the primary db (with no passphrase )
# and that you copied the /var/lib/postgresql/.ssh/id_dsa.pub into
# the /var/lib/postgresql/.ssh/authorized_keys on the standby
# host, in order to be able to ssh from your primary to your standby without
# authentication.
# - Your primary database is in archive mode and that the postgresql.conf
# contains :
# archive_command = 'cp "%p" /var/lib/postgresql/data/archives/"%f" && scp
-B "%p" mydb2:/var/lib/postgresql/data/archives_mydb1/"%f"'
#
# This script can probably run in other conditions, but in my case I had :
#- PGDATA=/var/lib/postgresql/data
#- database user : postgres
#- postgres home in /var/lib/postgres
#- sed
#- postgresql 8.1.2
#
#
# Known problems/missing features :
# - The rotate log is done only when the log is full (default 16M), this can
# be a problem on DB with few UPDATES/INSERT, because if the primary
# database fails, and that no log has been transmitted for several hours,
# the standby db is late of several hours.
# - You cannot stop the standby database simply by shutting it down, because
the
# recovery.sh script will wait forever... You need to :
# killall recovery.sh
# After the "killall recovery.sh" the standby database will go online,
except
# if you initiate a database shutdown before.
#
# François Delpierre 02/2006
export PGDATA='/var/lib/postgresql/data'
#export PGDATA_ROOT_SB='/var/lib/postgresql/'
export STANDBY_HOST='mydb2'
export DATE=`date`
DBNAME="mydbname"
echo "Put the primary DB in backup mode"
psql $DBNAME -c "SELECT pg_start_backup('Backup $DATE');"
echo "Stop the recovery process if running"
ssh $STANDBY_HOST "killall recovery.sh"
sleep 3
ssh $STANDBY_HOST "killall -9 recovery.sh"
sleep 2
echo "Stop the standby database"
ssh $STANDBY_HOST "PGDATA=$PGDATA pg_ctl stop -m immediate || echo 'WARNING :
Failed to stop standby DB'"
sleep 2
ssh $STANDBY_HOST "killall postmaster"
ssh $STANDBY_HOST "killall postgresql"
echo "Copy datafiles"
rsync -avc --exclude '*archives/' --exclude 'pg_log/' --exclude
postgresql.conf --exclude *pg_xlog/ --delete $PGDATA/ $STANDBY_HOST:$PGDATA/
echo "Stop the backup mode on primary DB"
psql $DBNAME -c "SELECT pg_stop_backup();"
echo "Copy the postgresql.conf file and change the archive command."
export PGDATAESC="`echo $PGDATA | sed -e 's/\//\//g'`"
cat $PGDATA/postgresql.conf | sed -e
"s/[[:space:]]*archive_command.*/archive_command='cp \"%p\"
$PGDATAESC\/archives\/\"%f\"'/" | ssh $STANDBY_HOST "cat - >
$PGDATA/postgresql.conf"
echo "Create the standby script on $STANDBY_HOST"
ssh $STANDBY_HOST "cat - > $PGDATA/standby.sh" < $PGDATA/recovery.sh < $PGDATA/recovery.conf < $PGDATA/postmaster.log 2>&1 On Sun, Feb 12, 2006 at 11:47:11AM +0100, [EMAIL PROTECTED] wrote:
> > Hello,
> >
> > I'm new to the list, and I'm intereted in PostgreSQL replication (using
> > WAL) As I didn't find any script to do that, I make mine, that seems to
> > work.
> >
> > However, I wonder how can I "switch WAL logs", in order to have the
> > standby database not too much late.
> > The database actually generates few logs, and I would like one WAL to be
> > generated once per hour.
> >
> > How Can I do that ?
>
> You can't. What you can do is copy the most recently touched log file as
> often as you'd like. That will limit your data loss should you need to
> fail-over.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[ADMIN] session variable and/or global variable in plpgsql
Hi, Is it possible to set a custom session variable and/or global variable in plpgsql ? Regards, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] help
help ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] problem creating user
On Mon, 12 Jan 2004, kandiah ratnavale wrote: > -bash-2.05b$ whoami > postgres > -bash-2.05b$ su - bruce > su: user bruce does not exist > -bash-2.05b$ createuser bruce > Shall the new user be allowed to create databases? (y/n) > Shall the new user be allowed to create more new users? (y/n) y > ERROR: CREATE USER: user name "bruce" already exists > createuser: creation of user "bruce" failed > -bash-2.05b$ su - bruce > su: user bruce does not exist > -bash-2.05b$ whoami > postgres > -bash-2.05b$ > > could you please tell me what is wrong here? > how would i change to user bruce? You are confusing OS users with postgres users. Createuser makes a postgres user; making OS users depends on your flavour of unix, but useradd will probably work for you. 'man useradd'. -j -- Jamie Lawrence[EMAIL PROTECTED] "In my little way, I'm sneakily helping people understand a bit more about the sort of people God likes." - Larry Wall. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Colors in vim
On Tue, 09 Mar 2004, Louie Kwan wrote: > Not a PGSQL question.. > > When I use vim, my xterm is all highlighted with many different color.. > > How can I turn the coloring off. > > Sorry to ask the vim question here. Numerous ways. In a session: :syntax off Presumably, you're using files with a file extension that is tripping the coloring highlights, probably provided by your OS. You can turn them off globally by adding a syntax command to your .vimrc. (It could be that you're editing files with embedded hints, or several other things.) See http://vimdoc.sourceforge.net/htmldoc/syntax.html#syntax for more details, or start with :help syntax within vim. -j -- Jamie Lawrence[EMAIL PROTECTED] Don't anthropomorphize computers. They hate that. ---(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
[ADMIN] IPv6 connection / JDBC (?)
Hi! I run pg7.4 on a SuSE 9.1 (IPv6) machine (linux 2.6); despite I added hostall all ::1 :::::::password to my pg_hba.conf, connecting to the database via JDBC (pg74.213.jdbc3.jar) fails: org.postgresql.util.PSQLException: A connection error has occurred: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "::1", user "foo", database "bar", SSL off ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] constraints and performance
On Wed, 11 Feb 2004, Christopher Browne wrote: > A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Jodi Kanter) wrote: > > Do constraints effect performance significantly? > > They would be expected to provide a significant enhancement to > performance over: > a) Firing triggers, > b) Firing rules, and > c) Forcing the application to validate the data, and then adding > in "data validation" reports to report on the cases where a buggy > application violated the constraints. > > So yes, they should be considerably faster than any of their > alternatives. A completely correct answer, but not the one I suspect Jodi wanted, which was whether there was a "significant" penalty difference between using constraints on a table and not using constraints on a table. I'm not sure I have any better answer, because we don't know what "significant" means, or the nature of the constraints. I will share my experience, which is that constraints add little noticable overhead in simple cases. However, when constraining cascading deletes through many tables, for instance, it is absolutely noticable. Between those two extremes, it isn't "too bad", for me and my applications, wherein I rely heavily on constraints (and rules, and server-side triggers). I don't think it is possible to say "constraints add an n% overhead", due to the extreme variability of the way they can be used. Best practice, as Christopher notes, indicates that they should be used. It saves a lot of grief (why write the code in the application layer when you can the DB authors already have?). If you are in a situation where the difference between using them and not using them forms a critical boundary, I would suggest you have some other problems, either in design or specification. I realize that may not be helpful, given real world constraints - the consumers of applications may not be realistic in setting requirements. One thing to think about carefully, if building an application that has to scale to any real degree, is the tradeoff between client side and server side processing. While it is best-practice to keep data validation close to the data, I have been involved in some projects where scaling the DB server to the task was not economically possible; the project would not have happened if that were an enforced criteria. Messy, bad, poor practice? Yes on all counts. One must be very, very careful if one chooses to ensure data integrity client-side. Anyway, getting back to the question, the only real answer is "try it and see". As far as I know, there's no way to quantify the impact of constraints on query performance without taking the data model and usage patterns of the application into account. I hope this helps some. -j -- Jamie Lawrence[EMAIL PROTECTED] There is nothing more demoralizing than a small but adequate income. - Edmund Wilson ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Installing PostgreSQL as "postgress" versus "root" Debate!
On Wed, 12 Jan 2005, Goulet, Dick wrote: > Whatever, I'll keep root only for absolutely restricted use & install > under a separate user account. Works just fine & it makes the auditors > & sysadmin feel better. Unfortunately, I _know_ how auditors think, but I would hope that a sensible company would have admins that know better. After a few years in that role... ah, probably better not to say. If you stop and look at the binaries you use as that separate account to do, well, just about anything - specifically who owns them, and why, you might find a few interesting thoughts about how actual unix security works bubbling around. But this is just technical advice, not career advice. -j, glad to be where he is now. -- Jamie Lawrence[EMAIL PROTECTED] I have come to believe that the whole world is an enigma, a harmless enigma that is made terrible by our own mad attempt to interpret it as though it had an underlying truth. - Umberto Eco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] configuring pg_hba.conf...
i'm an actual newbie when it comes to postgres. how do i set the pg_hba.conf for a password authentication? how do i set the ps_pwd and other needed configuration? and lastly, where can i find a detailed document on configuring postgres? thanks. samil buri.
Re: [ADMIN] configuring pg_hba.conf...
On Tue, 26 Jan 1999, Oliver Elphick wrote: > Postgres GOD wrote: > >how do i set the pg_hba.conf for a password authentication? how do i set > >the ps_pwd and other needed configuration? > > Here is a document I put together on the subject. (If anything in it has > become inaccurate, please let me know; I believe it is correct for 6.4.2.) > >+ > How to use clear or encrypted passwords for PostgreSQL access: > = > > Use lines such as > > local all password > host192.137.23 255.255.255.0 crypt > > in /etc/postgresql/pg_hba.conf; then you can use > >CREATE USER user WITH PASSWORD password... > thanks for your help. i followed your instructions but can't still connect to my database. btw, i'm using 6.3.2 version. i copied exactly the two lines you've mentioned above in my pg_hba.conf and i even tried modifying using my server's ip but still user authentication failed. what should be the problem with this? samil buri. --- 'winning w/o fighting is best.' --suntzu.
[ADMIN] vacuum problem...
i know this one is easy one to those postgres guru. how can i deal with this vacuum problem? NOTICE: CreatePortal: portal already exists ERROR: can't create lock file -- another vacuum cleaner running? thanks. samil. 'winning w/o fighting is best!'-suntzu.
[ADMIN] maximum attribute record.
i would like to know what is the maximum attribute record that can class can accomodate. i'm having problem with one of my table(class) it hangs-up during insertion. i'm using the libpq in my c script as the programming interface for postgres. samil buri.
[ADMIN] ACT! and PostgreSQL
I have some clients that are looking to upgrade to MSSQL Server because they use ACT!. I'm developing a web application for them that uses PostgreSQL. Does anyone know if ACT! can use PostgreSQL? or if I can hack it? Suggestion welcome and Thanks, J ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] PostgreSQL and Delphi 6
I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] $_SERVER['SERVER_ADDR'] ; returns wrong value "::1"
::1 is a function of the PHP $_SERVER[''] internal function, not a PostgreSQL problem. It will display ::1 on any database, you program a PHP custom function to call the whole IP address. The real question is that a problem in a production environment? Of note, outside IP addresses will display fine using $_SERVER['']. Good Luck, J ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] Data insert
Sorry for the attachment, but copying and pasting this data does not work. I don't have any idea how to insert the type of data into PostgreSQL. Basically, it's encrypted data in which I would like that keep raw format. Thanks for any help, J sample_data.pdf Description: Adobe PDF document ---(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
[ADMIN] plpgsql question
Can I do something like this: CREATE TABLE sample (id SERIAL, node INTEGER, parent INTEGER); INSERT INTO sample(node,parent) VALUES(1,0); INSERT INTO sample(node,parent) VALUES(2,0); INSERT INTO sample(node,parent) VALUES(3,1); INSERT INTO sample(node,parent) VALUES(4,3) CREATE OR REPLACE FUNCTION article_display(anyelement, anyelement) RETURNS SETOF samle AS $$ DECLARE articleRow sample%ROWTYPE; BEGIN FOR articleRow IN SELECT comments FROM theirry.articles ORDER BY article_id DESC LIMIT $1 OFFSET $2 LOOP RETURN NEXT articleRow; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; Thanks, J ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [ADMIN] plpgsql question
I have data in one table called articles and I would like to make a function in which takes certain data from it and display the results. Example: CREATE TABLE articles ( article_id serial, title varchar(200), posted timestamp, article_subject varchar(200), article_body text, allow_comments boolean, comments smallint ); I understand one way to display a results I would like is creating a TYPE with the columns needed. CREATE TYPE articles_output AS ( article_id int title varchar(200), article_body text, comments smallint ); Now I would like the function to display data using the LIMIT and OFFSET option ex: SELECT title, article_body, comments FROM articles ORDER BY article_id DESC *LIMIT 4 OFFSET 0*; this is function I created: CREATE OR REPLACE FUNCTION article_display(integer, integer) RETURNS SETOF article_output AS $$ DECLARE articleRow article_output%ROWTYPE; sampleRow RECORD; BEGIN FOR sampleRow IN SELECT title, article_body, comments FROM articles ORDER BY article_id DESC LIMIT $1 OFFSET $2 LOOP RETURN NEXT sampleRow; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; this is the error -> ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "article_sample" line 10 at return next Can I do this or are there better options? Thanks for the help, J ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] Admin Position in Las Vegas
Are you a good Unix/PostgreSQL admin with some MySQL experience? Do you want to help a company migrate off of MS SQL Server? Do you want to live and work in Las Vegas for a fun company of good computer techs? We need you. Contact me. -- Tim Ellis Senior Database Architect and author, tedia2sql (http://tedia2sql.tigris.org) If this helped you, http://svcs.affero.net/rm.php?r=philovivero ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] Binding Parameters
I'm new to PostgreSQL, and am wondering for BindSQL is intergrated in Postgres or in the future for version 8. Example: SELECT * FROM sample_table WHERE computer = :1; Any pointers will help. Thanks, J ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Binding Parameters
Do you mean PREPARE / EXECUTE?
/* [EMAIL PROTECTED]:5432/ahammond =# */ \d foo
~ Table "public.foo"
~ Column | Type | Modifiers
- +-+--
~ foo_id | integer | not null default nextval('foo_id_seq'::text)
~ name | text| not null
Indexes:
~"foo_id_idx" unique, btree (foo_id)
~"foo_name_idx" unique, btree (name)
/* [EMAIL PROTECTED]:5432/ahammond =# */ SELECT * FROM foo;
~ foo_id | name
- +--
~ 1 | a
~ 2 | b
~ 3 | c
~ 4 | d
~ 5 | f
(5 rows)
/* [EMAIL PROTECTED]:5432/ahammond =# */ PREPARE foo_name (integer) AS
SELECT name FROM foo WHERE foo_id = $1;
PREPARE
/* [EMAIL PROTECTED]:5432/ahammond =# */ EXECUTE foo_name (1);
~ name
- --
~ a
(1 row)
/* [EMAIL PROTECTED]:5432/ahammond =# */ EXECUTE foo_name (4);
~ name
- --
~ d
(1 row)
Thanks for the short tutorial... I think that I've gotten the point. I
was searching online and nothing proved useful.
Anymore more examples or direction on finding any of bind parameter is
appreciated.
J
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[ADMIN] PREPARE/EXECUTE questions
I'm writing a web application inwhich clients will login/out among other stuff. I have be reading on prepared statements and leave you all with an open-ended question. Are prepared statements better and how can I program them to not end at the once the session is over? Thanks, any other suggestions are welcome. J ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] PREPARE/EXECUTE questions... custom functions?
Joshua D. Drake wrote: Use a connection pooler like pgpool. Thanks for the suggestion, I will look into it and test. There seems to some overhead using that tool. What about custom functions, written in SQL or C? They seem to work until the database is shut down, any thoughts about that for a custom application? Pros/Cons... Thanks in advance, J ---(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
[ADMIN] SQL Functions
What are the advantages to SQL Functions? I'm looking to optimize 'default' PostgreSQL environments for clients. I'm writing an application and looking to put functions to optimize just that table, etc. They don't want to have a full-time database admin (surprise!). Thanks, J ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] help!!!!!!
You're php install isn't finding the postgresql libs it needs. When you do the ./configure --with-pgsql check the output of the config run, and you'll see something like: checking for PostgreSQL support... no You may want to set include flags when compiling CPPFLAGS=-I/usr/local/pgsql/include (or something like that) J ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Disk I/O Monitoring
On Fri, 2003-06-06 at 09:42, Kris Kiger wrote: > I'm looking for a good way to monitor disk I/O on a linux > system. Specifically, I would like to know when postgres is making > page-ins and page-outs. If anyone knows of a good utility, please let > me know! Thank you for the help vmstat. -- Tim Ellis Senior Database Architect and author, tedia2sql (http://tedia2sql.tigris.org) If this helped you, http://svcs.affero.net/rm.php?r=philovivero ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] Problem with regression testing
Hi, I've installed PostgreSQL on a RedHat 6.0 i386 box. The compile and everything seemed to go fine. I'm now logged in as the superuser (postgresql) following the install instructions in the docs. postmaster is indeed running. PGLIB and PGDATA are properly set (PGLIB=/usr/local/pgsql/lib and PGDATA=/usr/local/pgsql/data). But when I run "createdb" to test things out I get this: ... [postgres@catclaw bin]$ createdb Connection to database 'template1' failed. FATAL 1: Database template1 does not exist in pg_database createdb: database creation failed on postgres. ... And when I try to create a db user with createuser I get the same message: ... [postgres@catclaw bin]$ createuser Connection to database 'template1' failed. FATAL 1: Database template1 does not exist in pg_database createuser: database access failed. ... The only thing that bothers me is that the PostgreSQL package that comes bundled with RedHat 6.0 *was* installed when the system was initially set up. So there's a bunch of PostgreSQL executables in /usr/bin and there's existing PostgreSQL stuff residing in places like /var/lib. Conversely, *I've* been working with my own install downloaded from postgresql.org, the postgresql-6.5.1.tar.gz package. Could this problem I'm having be related to a conflict therein? Does anyone have any suggestions? Thanks, -Robin
[ADMIN] Steps for Record-based Log Shipping postgres 8.2
Hi, What are steps/code for Record-based Log Shipping postgres 8.2? If use Record-based Log Shipping can I access both primary and standby server simultaniously. Tarun -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Regaining superuser access
On Fri, September 19, 2008 07:39, Scott Marlowe wrote: > On Fri, Sep 19, 2008 at 2:26 AM, Bernt Drange <[EMAIL PROTECTED]> wrote: >> On Sep 18, 7:03 pm, [EMAIL PROTECTED] (Alvaro Herrera) wrote: >>> Bernt Drange escribió: >>> >>> > After a lot of fiddling with being able to enter single user mode on >>> a >>> > windows machine (I had to figure out how to run the command line as >>> > the correct user, then for some reason -D didn't work, but SET >>> > PGDATA=xxx worked), I finally managed to fix my problem. >>> >>> Hmm, the -D thing not working should probably be studied -- perhaps >>> we're missing escaping something somewhere. Does the PGDATA path >>> contain spaces or weird chars? >> >> From memory the path was something like: F:\Postgresql Database\data. >> I quoted it with double quotes. Without -D postgres.exe complained >> about not finding the data path, with it postgres.exe complained about >> not finding the config file, stating that it looked in (from memory >> vague) F:\Postgresql Database\data\postgres\somethingmore. Adding the >> --config-file parameter didn't help. >> >> Is this enough information for you to start digging a bit more? If >> not, I might find the exact messages, but I'm reluctant to do it on >> this production database.. > > I'm pretty sure the problem is with the space between Postgresql and > Database. Not sure if it's fixed in later releases or not. > > -- > Sent via pgsql-admin mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > Part of the problem may be the embedded space, as was already mentioned (even though it shouldn't be an issue). A good test would be to use the 8.3 directory naming convention which you can get on Windows by using the "dir /x" command. On my system, "C:\Program Files\" is shortened to "C:\PROGRA~1\". Obviously, you'd have to look at every directory in the fully qualified filename to pull the 8.3 pathname. And I'll go back to lurking and learning on the list. Tim KB0ODU -- Timothy J. Bruce visit my Website at: http://www.tbruce.com Registered Linux User #325725 -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Upgrade in same major version
On Thu, October 16, 2008 06:47, Carol Walter wrote: > Let me see if I understand this correctly. > Always do a backup before doing any destructive sys admin functions. =) > First, I run configure, taking care to make sure all the options point > to the right places. > Second, I run gmake > Third, I run gmake install. > > Thanks, > Carol > > On Oct 14, 2008, at 1:22 PM, Evan Rempel wrote: > >> What this means is that you do not have to "update" the data >> repository (wherever your >> postgresql database is stored). All that needs to be done is to >> uninstall the old version, >> and install the new version. Start the new version and use the data >> where it sits. >> >> Now, that all sound fine when I say it fast. There are a number of >> precautions that >> you may prefer to take, and the steps may differ significantly >> depending on your system. >> >> 1. Probably take a complete backup of your database should something >> go wrong. >> 2. Ensure that you have a way to "get back" to the previous version, >> or have >> a very reliable way to move forward, such as taking a pgdump >> that can be loaded >> back into the database, nearly regardless of version. >> 3. Your uninstall may be a package management step such as rpm, or >> you may >> just delete the files if your install was done from source. >> 4. Your install may be a package management step such as rpm, or you >> may >> require a "sudo make install" if your install was done from >> source. >> >> I hope this points you in mostly the right direction without begin >> to long >> winded. >> >> Evan. >> >> Carol Walter wrote: >>> Hello, >>> >>> I'm doing an upgrade from 8.2.4 to 8.2.10. The documentation says, >>> "When you update between compatible versions, you can simply replace >>> the executables and reuse the data directory on disk." I guess I >>> don't quite understand what this means. Replace them by running some >>> parts of the installation process? Replace them by copying them. >>> Are you talking about those files that usually reside in /usr/local/ >>> pgsql/bin and include psql and pg_ctl? >>> >>> I know these may seem like dumb questions to those of you who are old >>> hands at this. I'd be glad to read more documentation if there is >>> some that is more explicit than what I quoted above. >>> >>> Thanks so much, >>> >>> Carol >>> >>> -- >>> Sent via pgsql-admin mailing list ([email protected]) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-admin >> >> >> -- >> Evan Rempel [EMAIL PROTECTED] >> Senior Programmer Analyst250.721.7691 >> University Systems,University of Victoria >> >> >> >> -- >> Sent via pgsql-admin mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-admin > > > -- > Sent via pgsql-admin mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > As part of your backup, make sure you also backup your postgresql.conf and pg_hba.conf if they are in your data directory. These are done at the Operating System level using the cp (copy) command. They shouldn't be overwritten, but having a backup copy of those is always nice for when Murphy strikes. Tim -- Timothy J. Bruce visit my Website at: http://www.tbruce.com Registered Linux User #325725 -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Need help in enabling remote connection
On Tue, November 3, 2009 10:10, Scott Marlowe wrote: > On Tue, Nov 3, 2009 at 1:53 AM, Shruthi A wrote: >> Hi all, >> >> -- Once this service is started, telneting the server on the given port >> successfully connects. >> -- Dear Ian Lea, i guess 10.0.0.1/24 is the same as 10.0.0.0/24 as the >> mask '24' means that the first 3 octets (24 bits from the left hand >> side) >> must match ie both mean simply 10.0.0.* > > Actually 10.0.0.0/24 means that the right hand 24 bits don't matter, > so anything starting with 10. will match. > Actually, it is the 24 left-most bits that matter, so this sets up a network of 24 bits with 255 hosts that can connect. I wasn't sure of this, so I just verified it with one of our network engineers at work. Tim -- Timothy J. Bruce Registered Linux User #325725 -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Postgres recommended by a SQL Server DBA
An interesting write-up coming from a SQL Server DBA. http://facility9.com/2011/12/ten-reasons-postgresql-is-better-than-sql-server/ Tim -- Timothy J. Bruce -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] retaining useful information on my screen
On Tue, May 8, 2012 07:28, Fred Parkinson wrote: > I am currently using postgresql version 9.1.3 > > In the past, when I ran psql and needed useful information provided by > commands like \h or \d, the information was presented on the screen and I > could proceed with further commands while viewing the information. > > Now I need to hit -q to get out of the \h or \d request, and the > information disappears from the screen as well. > > 2 questions: > 1. what was the problem presented by leaving the information up on the > screen, which is now solved by removing the information from the screen? > > 2. Is there way to tell psql NOT to clear the screen, so I can > subsequently view it while I work? > > Thanks. > > > Fred Parkinson > Application Programmer > Association of Bay Area Governments > 510-464-7931 > Set the environment variable PAGER to solve this problem. I usually set it with the following command in my .bashrc file export PAGER='less -X' NOTE: That's an uppercase X after the Unix command "less." -- Timothy J. Bruce -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Problem with regression testing
Problem solved, thank you for the responses! Yes, I had run initdb as outlined in the instructions. Then I received a tip from someone on the list suggesting: "do: rpm -qa | grep postgres then, for each package it returns: rpm -e " Which I did. At that point, running createdb gave me a *new* error message that didn't refer to pg_database or template1. On a whim I ran initdb again and got this response from the system which is understandable since I had already run it: >>> We are initializing the database system with username postgres (uid=100). This user will own all the files and must also own the server process. initdb: error: File /usr/local/pgsql/data/PG_VERSION already exists. This probably means initdb has already been run and the database system already exists. If you want to create a new database system, either remove the directory /usr/local/pgsql/data or run initdb with a --pgdata option other than /usr/local/pgsql/data. <<< So I removed the directory /usr/local/pgsql/data and ran initdb again. It did it's thing again with pg_database and template1. At this point running createdb gave me an error about not being able to open the postgres libraries, so I added the libraries' path to /etc/ld.so.conf and from there was off and running! Thanks again for the help, -Robin On Fri, 10 Sep 1999, Jacques B. Dimanche wrote: > At 07:42 PM 9/9/99 -0700, Postgres Mailing List wrote: > >is indeed running. PGLIB and PGDATA are properly set > >(PGLIB=/usr/local/pgsql/lib and PGDATA=/usr/local/pgsql/data). But when I > >run "createdb" to test things out I get this: > >FATAL 1: Database template1 does not exist in pg_database > > H... sounds like you forgot to run "initdb" which will create the > template1 database for you and set things up. Did you run that first? > > Sincerely Yours, > > Jacques Dimanche > VP of Research and Development > Tridel Technologies, Inc. > > >
[ADMIN] Enormous pg_sorttemp~ files when using "OR"
Hi Folks, When I write a query with a "WHERE" claused constrained by the "OR" operator, postgresql creates the enormous pg_sorttemp~ files in ~pgsql/data/base/dbName/ that basically eat up all remaining disk space in a matter of seconds. These files are 1.8GB+ by the time the partition is full. Obviously, I need to rewrite these queries or break them up into multiple queries. I was posting here to see if anyone with more experience might have a suggestion or comment on this - like a better way to write queries that otherwise would use "OR", or maybe a configuration or hack tip on how to prevent postgresql from running wild by building such huge sorttemp files. Thanks, -Robin
Re: [ADMIN] Enormous pg_sorttemp~ files when using "OR"
Oh yeah, sorry - I forgot to comment on my environment: -PostgreSQL 6.5.2 -RedHat 6.0 -Intel Pentium II 400mhz 160mb RAM -Java/JDBC On Sat, 18 Dec 1999, Andy Lewis wrote: > This has also happened to me. > > I had another programmer write a query that was requesting a field from a > table but, forgot to include the name of the table in the query. It was a > 3 table query and he accidentally included a fourth table field but didn't > add the table in the clause. > > I was suprised to see postgres not error out. Instead it sucked all of the > CPU from the machine threw the pg_sort files all of the drive and > eventually filled up the drive. > > Very strange. Perhaps the Postgres Team can take note and fix for future. > > Another thing that I am seeing alot of is fields labeled as serial and > having duplicate oid's. It happens on 3 of my 4 machines on a monthly > basis. This really needs to be fixed because vacuum won't run without > the serial fields being unique. > > All of the above running 6.5.3 > > Andy > > On Sat, 18 Dec 1999, Robin's Postgres List Archive wrote: > > > > > Hi Folks, > > > > When I write a query with a "WHERE" claused constrained by the "OR" > > operator, postgresql creates the enormous pg_sorttemp~ files in > > ~pgsql/data/base/dbName/ that basically eat up all remaining disk space in > > a matter of seconds. These files are 1.8GB+ by the time the partition is > > full. > > > > Obviously, I need to rewrite these queries or break them up into multiple > > queries. I was posting here to see if anyone with more experience might > > have a suggestion or comment on this - like a better way to write queries > > that otherwise would use "OR", or maybe a configuration or hack tip on how > > to prevent postgresql from running wild by building such huge sorttemp > > files. > > > > Thanks, > > > > -Robin > > > > > > > > >
[ADMIN] Unfamiliar SQL Exceptions
The following two SQL Exceptions are popping up after several days or weeks of seemingly smooth execution: "Unknown Response Type x" <= where x is a random ASCII character and "The column index is out of range" Once one of these two SQL Exceptions start occurring, pretty much all queries afterwards produce the same error and I have to restart the web application and/or postmaster. Can anyone shed any light on these and what may be causing them? RedHat Linux 6.2 PostgreSQL 6.5.2 The web app is Java Servlets & JSP w/JDBC & with a basic connection pool implementation Thanks, -Robin
[ADMIN] Data Corruption?
Hello, After about a year and a half of use, I ran into a peculiar problem with my PostgreSQL driven app and I would like to understand why. I have a web app that uses PostgreSQL 6.5.2 on the backend. The app logic is Java beans and JSP. The OS is RedHat 6.2 i386. Anyways, there are two identical databases that are simultaneously accessed by the application for any type of data transaction, for redundancy purposes. Last month, I started getting various inexplicable error messages both with the web app and with psql. The most incriminating were these two: ERROR: carcustomer: Table does not exist ~and~ FATAL 1: Memory exhausted in AllocSetAlloc() In psql, for example, simple queries would hang for awhile and then produce the memory error message. After doing some troubleshooting, I dropped the table that seemed to be at least in part the culprit, the "car" table, and then replaced it with a dump of the same table from the secondary mirror database. At that point, the error messages ceased and everything has worked great for the last 3 weeks. Now I need to understand why this happened so I can address it properly. Does anybody know what may have caused this? Thank you, -Robin
