Re: [ADMIN] Add user on old database with password
> 1) create one user with password and all Grant on Database. it is a admin > user The below steps would help: $ su - postgres $ createuser your_user Shall the new user be allowed to create databases? (y/n) y Shall the new user be allowed to create more new users? (y/n) y CREATE USER Use GRANT command to grant privileges to specified user. > 2) i want to add password to postgres user. 1) If the authentication mode is trust then it is simple. Connect to any database as postgres and using alter user you must be able to set password for postgres user. 2) If the authentication mode is any other, then you may not be able to connect to any database as postgres user, since you didn't set password for postgres user. So, set the authentication mode to trust and restart postmaster and repeat step one to set password for postgres user. Finally restore the authentication mode. regards, bhuvaneswaran ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] DUMPall PG7.1.3 restore to PG7.3.2
> > Postgres 7.3.X is missing of a lot implicit cast present before > so I suggest you to define your cast integer => varchar before > to import the DB. > do you mean I redefine this function varchar(integer)-into> varchar(varchar) ? because this function varchar(integer) is from PostgreSQL 7.1.3 itself best regards Eko Pranoto ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Timestamp of insertion of the row.
On Fri, Jun 13, 2003 at 00:17:38 +0200, Mendola Gaetano <[EMAIL PROTECTED]> wrote: > "Henry House" <[EMAIL PROTECTED]> wrote: > > Yes. Easy answer: use a column of type 'timestamp default now()'. > > With that default value you store the time > stamp of transaction where the row was inserted. Immagine to insert > inside the same transaction a lot of rows and this operation will take long > 1 minute, you'll have all rows with the same time stamp instead of time > stamp spreaded inside that minute, use timeofday instead. You still may not want to use timeofday even for long transactions. It depends on what the data really means to you. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] statistics question
can statistics help the postgresql performance? if yes how?
Re: [ADMIN] Postgres Size-limit
On Tue, 10 Jun 2003, Karien wrote: > Hi there. > > First things first. I just want to say I've moved from Mysql to Postgres > for alot of reasons. :-) > > My questions: > = > > * What is the limit my PostgresDB can grow to? 4gigs, 8gigs? Waaayy bigger. While the theoretical limit is truly huge, in real life, there are examples of databases of hundreds of terabytes running underneath postgresql. > * What sort of system I'am a looking at running postgres on? >From a 486-50 with 16 Meg of ram to a 64 way Sun E10k with 64 gigs of RAM, or a Z990 mainframe with 32 CPUs. And anything in between. > Now here's a really confusing one :-) > > * Can just can't decide which OS to use here: > > FreeBSD or Linux > > Which OS would you guys chose? I would choose Linux because I know it well, and I know its pitfalls and how to avoid them. Look at your "local support" i.e. the other computer folks you interact with. If there's lots of expertise in BSD around you, then pick that. Note that Postgresql also runs on Solaris HPUX AIX Mac OSX SCO Openserver SunOS4 Tru64 Unixware Windows So if you need to migrate to big iron, you can. > Last question > == > > * Where can I find some real good tests done on Postgres which will > indicate: > > - How fast Postgres really works > - Postgres and other DB compared > - Database UPTIME > - Speed and complexity of DB http://sourceforge.net/projects/osdb/ is considered one of the better benchmarks out there. My personal experience, in running Postgresql for four years has been zero unscheduled down time, and zero data loss. And it's quite fast as long as you know not to do certain things (i.e. select max(id) from tableofabillionrows). The last version of Postgresql I was able to bring down with a bad query (think unconstrained joins on several million row tables) was 6.5.3. My advice is to thouroughly test your server before putting it online. just because the vendor told you all the memory was good doesn't mean it is. see www.memtest86.com for a good memory tester. ---(end of broadcast)--- TIP 9: most folks find a random_page_cost between 1 or 2 is ideal
[ADMIN] list ranking
Title: We have a set of records in a table that needs to be ranked. We thought of adding a ranking field, but the problem is that the ranking could change often and there could be additions and deletions from the list. Updating this ranking field is quickly going to get messy. Is anyone familiar with link surgery? Can we do this with a doubly linked list? Is there a standard database solution for doubly linked lists? or another way to solve this that I don't see? Any suggestions on structure would be greatly appreciated. Thanks Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
[ADMIN] LAST REQUEST: Remove
This list does not make it easy to unsubscribe. I have asked to be removed and I got an email telling me to e-mail a mailing address to be removed. I was told that I would get a e-mail to reply to to confirm, and I did not get any such mail. I have already asked to be removed 3 times. I see others are having the same problem. Please, will an admin remove me manualy. Thank you. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Run 4 postgresql session on ONE server?
On Thu, 12 Jun 2003, Daniel Seichter wrote: > Hello, > I only know the traditional way of using postgreSQL: > In serverprocess with an "unlimited" number of databases. > But: > Is is possible to have 4 processes, for each database one, running on one > linux server? > > The reason is the following: > I only have one server on which a productive database is working and a > second database, which will be set upped at the moment. > On my productive database I can't test with the parameters so I am searching > for a way, that I can do the following: > - let running for each database one serverprocess on the same machine > - having for each database one pg_hba.conf and postgresql.conf (on testdb's, > I want to log every SQL-statement, which is on a productive database > impossible to log) > > I know, that you shouldn't test on a productive server, but at the moment > this is the only way I can do but I hope to get an test server or a new > productive server as soon as the second database is ready. Yes, you can do it. All you have to do is create four seperate accounts for it to run under (pgsql1, pgsql2, pgsql3, pgsql4) and then in each of those accounts, set up a different PGDATA value and initdb as that user. Then edit each account's postgresql.conf to have a different port number (I just incremented from 5432 to 5433 etc...) and start them up. Then when you connect just specify the port of the database instance you need. Since each one is running as a different user with different postgresql.conf and pg_hba.conf files you can lock the production instance down tight to prevent brain farts (oops, I just dropped a table in the production database) and you're gold. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Run 4 postgresql session on ONE server?
"scott.marlowe" <[EMAIL PROTECTED]> writes: > Yes, you can do it. All you have to do is create four seperate accounts > for it to run under (pgsql1, pgsql2, pgsql3, pgsql4) and then in each of > those accounts, set up a different PGDATA value and initdb as that user. > Then edit each account's postgresql.conf to have a different port number > (I just incremented from 5432 to 5433 etc...) and start them up. You don't really need N users, unless you have more protection concerns than you mentioned (like you want each database to have its own DBA with no access to the other ones). A single "postgres" Unix userid can serve for all the postmasters in typical cases. All you really need are a separate data directory and a separate port number for each postmaster. Pay attention though to the amount of machine resources you are committing to each postmaster. You'd probably not want to push shared_buffers up real far, for example. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] list ranking
Jodi Kanter wrote: We have a set of records in a table that needs to be ranked. We thought of adding a ranking field, but the problem is that the ranking could change often and there could be additions and deletions from the list. Updating this ranking field is quickly going to get messy. Is anyone familiar with link surgery? Can we do this with a doubly linked list? Is there a standard database solution for doubly linked lists? or another way to solve this that I don't see? Any suggestions on structure would be greatly appreciated. It sounds to me like you just need a way to enforce a sort order that is able to handle insertions into the middle of the list? What about something like this: create table foo(f1 int, f2 text); create unique index foo_idx1 on foo(f2); insert into foo values(1,''); insert into foo values(2,'0001'); -- -- insert new values in between '' and '0001' insert into foo values(3,'.'); insert into foo values(4,'.0001'); -- -- we happen to need one right between those last two insert into foo values(5,'..'); -- -- now get them back in rank order regression=# select * from foo order by f2; f1 | f2 + 1 | 3 | . 5 | .. 4 | .0001 2 | 0001 (5 rows) You might want to periodically run a maintenance script that collapses the segments (i.e. turn f2 above into '', '0001', '0002', '0003', and '0004'). HTH, Joe ---(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
