Re: [GENERAL] Newbie questions relating to transactions
On Sunday, March 08, 2009, Tom Lane wrote: > Carl Sopchak writes: > > Here's what's around the error message in the log: > > > > SPI Proc: 3154128080 total in 398 blocks; 13664 free (178 chunks); > > 3154114416 used > > Hmm, so apparently some internal leak within the plpgsql engine. I'd be > willing to look into this if you can provide a self-contained test case. > (I don't wish to spend time trying to reverse engineer suitable tables > and data from the fragmentary function you posted, even assuming that it > would show the leak ...) > > regards, tom lane Yeah, those numbers looked pretty big to me, too... (Even though I didn't really know what they mean...) I'll try to put together a test case, but don't hold your breath. I'm kinda under a deadline on the project I'm working on, and this is a side track at best. There are other ways for me to work around this (client side script or changing the function to do partial runs at a time) that will get me to where I need to be. On the other hand, I like to help solve these types of things, so I should get around to it eventually... Thanks for your help. Carl -- 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] Newbie questions relating to transactions
Carl Sopchak writes: > Here's what's around the error message in the log: > SPI Proc: 3154128080 total in 398 blocks; 13664 free (178 chunks); > 3154114416 used Hmm, so apparently some internal leak within the plpgsql engine. I'd be willing to look into this if you can provide a self-contained test case. (I don't wish to spend time trying to reverse engineer suitable tables and data from the fragmentary function you posted, even assuming that it would show the leak ...) 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] Newbie questions relating to transactions
On Sunday, March 08, 2009, Tom Lane wrote: > Carl Sopchak writes: > > On Sunday, March 08, 2009, Gregory Stark wrote: > >> What do you mean you're running out of memory? > > > > "ERROR: Out of Memory" is what I meant when I said I was running out of > > memory! :-) This is returned by psql, but it is the postmaster process > > that is hitting the wall. > > Oh? Postgres doesn't spell its out-of-memory errors that way. Please > quote the *exact* message you got, not some approximation. Sorry. I didn't think capitalization would matter. It takes hours for this thing to run, so I did type the message from memory. Here's a cut and paste: "ERROR: out of memory" followed by "DETAIL: Failed on request of size 14." (quotes mine). > > Also, if this was a server-side out-of-memory problem, there should be a > memory usage dump appearing in the postmaster log, which would help > identify exactly what the problem is. Here's what's around the error message in the log: TopMemoryContext: 1007952 total in 14 blocks; 8568 free (10 chunks); 999384 used Local Buffer Lookup Table: 8192 total in 1 blocks; 744 free (0 chunks); 7448 used Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used Operator lookup cache: 24576 total in 2 blocks; 14072 free (6 chunks); 10504 used PL/PgSQL function context: 122880 total in 4 blocks; 21040 free (45 chunks); 101840 used Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344 used CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used Rendezvous variable hash: 8192 total in 1 blocks; 3848 free (0 chunks); 4344 used PLpgSQL function cache: 24328 total in 2 blocks; 5904 free (0 chunks); 18424 used TopTransactionContext: 8192 total in 1 blocks; 680 free (0 chunks); 7512 used ExecutorState: 253952 total in 5 blocks; 111296 free (8 chunks); 142656 used ExprContext: 8192 total in 1 blocks; 8112 free (0 chunks); 80 used SPI Exec: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used SPI Proc: 3154128080 total in 398 blocks; 13664 free (178 chunks); 3154114416 used MessageContext: 8192 total in 1 blocks; 4616 free (1 chunks); 3576 used smgr relation table: 8192 total in 1 blocks; 1776 free (0 chunks); 6416 used TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks); 16 used Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used PortalMemory: 8192 total in 1 blocks; 7904 free (0 chunks); 288 used PortalHeapMemory: 7168 total in 3 blocks; 2688 free (0 chunks); 4480 used ExecutorState: 32832 total in 3 blocks; 15672 free (3 chunks); 17160 used TIDBitmap: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used TupleSort: 319512 total in 6 blocks; 47720 free (4 chunks); 271792 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used PortalHeapMemory: 1024 total in 1 blocks; 880 free (0 chunks); 144 used ExecutorState: 8192 total in 1 blocks; 5984 free (4 chunks); 2208 used ExprContext: 8192 total in 1 blocks; 8176 free (7 chunks); 16 used Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used CacheMemoryContext: 667472 total in 20 blocks; 71416 free (0 chunks); 596056 used CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used CachedPlan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used CachedPlanSource: 3072 total in 2 blocks; 1056 free (0 chunks); 2016 used SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used CachedPlan: 3072 total in 2 blocks; 520 free (0 chunks); 2552 used CachedPlanSource: 1024 total in 1 blocks; 144 free (0 chunks); 880 used SPI Plan: 1024 total in 1 blocks; 888 free (0 chunks); 136 used CachedPlan: 1024 total in 1 blocks; 56 free (0 chunks); 968 used CachedPlanSource: 3072 total in 2 blocks; 1696 free (0 chunks); 1376 used SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used CachedPlan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used CachedPlanSource: 3072 total in 2 blocks; 1872 free (2 chunks); 1200 used SPI Plan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used CachedPlan: 1024 total in 1 blocks; 232 free (0 chunks); 792 used CachedPlanSource: 3072 total in 2 blocks; 1872 free (2 chunks); 1200 used SPI Plan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
Re: [GENERAL] Newbie questions relating to transactions
On Sunday, March 08, 2009, Alvaro Herrera wrote:
> Carl Sopchak wrote:
> > Well, the upgrade to 8.3 seemed to rid me of the command limit, but now
> > I'm running out of memory. I have 2Gb physical and 8Gb swap (after
> > adding 4Gb).
>
> Do you have AFTER triggers on the involved tables? They are recorded on
> memory and we have no mechanism to spill to disk, so it's frequent that
> those cause out-of-memory. If that's the explanation, your workaround
> would be to get rid of them.
No triggers have been defined on any tables in the database...
>
> > Is there a way for me to run this outside of one huge transaction? This
> > really shouldn't be using more than a few hundred megs of RAM (assuming
> > cursor records are all stored in memory)...
>
> Hmm, maybe you're holding too many cursors open and not closing them
> timely? Did you post your function for review?
I am only using one cursor, which is opened and closed repeatedly. It pulls
the base data from the database for the calculations.
I have not posted the function for review yet because its function is
proprietary. However, I have stripped out the proprietary stuff, and include
the code below. I marked everything stripped out by placing a brief
description enclosed within {{ and }}. I left all of the places that the
database is accessed in the code. I changed some of the line wrapping to fit
a reasonable width (which I mention in case you see syntax type errors).
There is a few lines that save the calculation details based on a flag in the
trial_header table. This flag is set to N for the run that I am having
issues with, so these records are not being created. I left that code in
below for completeness...
I realize this isn't probably the cleanest code out there (I'm sure using
prepared statements would help speed), but it was really meant to be a "quick
and dirty" way to calculate the data I need. Any comments or suggestions on
improving the code is welcome.
create or replace function Run_Trial (tid integer) returns void as $proc$
declare
{{ declarations }}
begin
-- Set start time...
program_version := '1.16';
update trial_header set start_timestamp = clock_timestamp(),
run_version = program_version, end_timestamp = null
where trial_id = tid;
-- get rid of prior run, if any:
delete from trial_results where trial_id = tid;
delete from trial_calc_detail where trial_id = tid;
-- Get the trial parameters:
select * into trial_hdr from trial_header where trial_id = tid;
{{ Do some calculations }}
-- Create temp table of data. This simplifies the coding below A LOT.
{{ conditional calc }}
-- (I can't figure out how to do this with a dynamic select and
-- "insert into trial_data select" and I get an error
-- if I make the dynamic SQL a "select into temp table trial_data"...)
-- Do it by brute force, I guess...
drop table if exists trial_data;
create temp table trial_data(
{{ fields }}
);
for row in execute
'select {{ select statement }}' loop
execute 'insert into trial_data values(' || {{ fields }} || ')';
end loop;
create index trial_data_idx on trial_data (data_mm, data_date);
create index trial_data_idx2 on trial_data (data_date);
-- Get date range for the data set we're using
for row in execute 'select min(data_date) as min_date,
max(data_date) as max_date from trial_data' loop
low_data_date := row.min_date;
high_data_date := row.max_date;
end loop;
-- Calculate maximum number of years that data covers
max_years = floor((high_data_date - low_data_date) / 365.25);
-- Loop through all possible "x year" periods
for cur_years in 1 .. max_years loop
-- start from the first period on file:
next_iteration_start := low_data_date;
num_periods := trial_hdr.periods_per_year * cur_years
+ trial_hdr.{{ field }};
for row in execute 'select count(*) as cnt from (
select data_date from trial_data where data_date >= ' ||
quote_literal(next_iteration_start) ||
' Limit ' || to_char(num_periods, '99') || ') a' loop
data_periods := row.cnt;
end loop;
-- Do each "x year" period in data
while data_periods = num_periods loop
-- Initialize calculation
-- used to set sucessive values for next_iteration_start:
iteration_counter := 0;
{{ some calculations }}
for row in execute 'select max(data_date) as max_date' ||
' from ( select data_date from trial_data' ||
' where data_date >= ' ||
quote_literal(next_iteration_start) ||
' order by data_date' ||
' Limit ' || to_char(num_periods, '9') || ') a' loop
per_end_date := row.max_date;
end loop;
-- Get dat
Re: [GENERAL] Newbie questions relating to transactions
Carl Sopchak wrote on 08.03.2009 17:37: or a way to run a function outside an implicit transaction No sensible DBMS will let you do _anything_ outside a transaction Thomas -- 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] Newbie questions relating to transactions
Carl Sopchak writes: > On Sunday, March 08, 2009, Gregory Stark wrote: >> What do you mean you're running out of memory? > "ERROR: Out of Memory" is what I meant when I said I was running out of > memory! :-) This is returned by psql, but it is the postmaster process that > is hitting the wall. Oh? Postgres doesn't spell its out-of-memory errors that way. Please quote the *exact* message you got, not some approximation. Also, if this was a server-side out-of-memory problem, there should be a memory usage dump appearing in the postmaster log, which would help identify exactly what the problem is. 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] Newbie questions relating to transactions
On Sunday, March 08, 2009, Gregory Stark wrote: > Carl Sopchak writes: > > Well, the upgrade to 8.3 seemed to rid me of the command limit, but now > > I'm running out of memory. I have 2Gb physical and 8Gb swap (after > > adding 4Gb). > > What do you mean you're running out of memory? For most part of Postgres > that's only a problem if you've configured it to use more memory than your > system can handle -- such as setting work_mem or shared_buffers too large. "ERROR: Out of Memory" is what I meant when I said I was running out of memory! :-) This is returned by psql, but it is the postmaster process that is hitting the wall. I haven't touched the configuration, so whatever the default in 8.3 is, is what these are set at. I'll look and bring the numbers down if necessary. Thanks for the pointer. > > One area that can cause problems is having too many trigger executions > queued up. I don't know if that's what you're running into though. There are no triggers on any of the tables in the database... > > > Is there a way for me to run this outside of one huge transaction? This > > really shouldn't be using more than a few hundred megs of RAM (assuming > > cursor records are all stored in memory)... > > Personally I find it much more flexible to implement these types of jobs as > external scripts connecting as a client. That lets you stop/start > transactions freely. It also allows you to open multiple connections or run > the client-side code on a separate machine which can have different > resources available. I suppose I could go that route... I was just trying to keep things simple, and all in the database. This was supposed to be a "quick and dirty" way to calculate, store and access these numbers... So much for "quick"... If there were stored procedures as in other databases that I've used, or a way to run a function outside an implicit transaction, then I'd be all set. Guess I'll have to add a layer to the setup. Got any suggestions as to a good, fast, language to code the external script in? Thanks for the help, Carl -- 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] Newbie questions relating to transactions
Carl Sopchak wrote: > Well, the upgrade to 8.3 seemed to rid me of the command limit, but now I'm > running out of memory. I have 2Gb physical and 8Gb swap (after adding 4Gb). Do you have AFTER triggers on the involved tables? They are recorded on memory and we have no mechanism to spill to disk, so it's frequent that those cause out-of-memory. If that's the explanation, your workaround would be to get rid of them. > Is there a way for me to run this outside of one huge transaction? This > really shouldn't be using more than a few hundred megs of RAM (assuming > cursor records are all stored in memory)... Hmm, maybe you're holding too many cursors open and not closing them timely? Did you post your function for review? -- Alvaro Herrerahttp://www.CommandPrompt.com/ 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] Newbie questions relating to transactions
Carl Sopchak writes: > Well, the upgrade to 8.3 seemed to rid me of the command limit, but now I'm > running out of memory. I have 2Gb physical and 8Gb swap (after adding 4Gb). What do you mean you're running out of memory? For most part of Postgres that's only a problem if you've configured it to use more memory than your system can handle -- such as setting work_mem or shared_buffers too large. One area that can cause problems is having too many trigger executions queued up. I don't know if that's what you're running into though. > Is there a way for me to run this outside of one huge transaction? This > really shouldn't be using more than a few hundred megs of RAM (assuming > cursor records are all stored in memory)... Personally I find it much more flexible to implement these types of jobs as external scripts connecting as a client. That lets you stop/start transactions freely. It also allows you to open multiple connections or run the client-side code on a separate machine which can have different resources available. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication 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] Newbie questions relating to transactions
Well, the upgrade to 8.3 seemed to rid me of the command limit, but now I'm running out of memory. I have 2Gb physical and 8Gb swap (after adding 4Gb). Is there a way for me to run this outside of one huge transaction? This really shouldn't be using more than a few hundred megs of RAM (assuming cursor records are all stored in memory)... Thanks for the help, Carl On Saturday, March 07, 2009, Tom Lane wrote: > Carl Sopchak writes: > > I have written a PL/pgSQL function that performs these calculations by > > reading the needed data, calculating, and saving the results. When run > > over a smaller set of data, it works fine. But when I tried to run it > > over this larger set of data, I got the error message "ERROR: cannot > > have more than 2^32-1 commands in a transaction". > > > > I have looked into trying to control the transaction within my function, > > but apparently this results in nested transactions, which is not > > supported by pgsql 8.2 (my current version). > > Try updating to 8.3 --- it only counts plpgsql statements as separate > "commands" if they actually modified something on-disk. The 2^32 limit > is still there but it's a lot harder to hit. > > 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] Newbie questions relating to transactions
Carl Sopchak writes: > I have written a PL/pgSQL function that performs these calculations by > reading > the needed data, calculating, and saving the results. When run over a > smaller set of data, it works fine. But when I tried to run it over this > larger set of data, I got the error message "ERROR: cannot have more than > 2^32-1 commands in a transaction". > I have looked into trying to control the transaction within my function, but > apparently this results in nested transactions, which is not supported by > pgsql 8.2 (my current version). Try updating to 8.3 --- it only counts plpgsql statements as separate "commands" if they actually modified something on-disk. The 2^32 limit is still there but it's a lot harder to hit. 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] Newbie questions (pg_global, tablespace, pg_temp, ...)
On Wed, Jan 17, 2007 at 07:02:13PM +0530, Alexi Gen wrote: > Hello, > > I'm new to postgreSQL (not databases) and trying to find my way arouund. > Have a couple of questions: > > (a) In which dir is the pg_global stored? > Is it "C:\Program Files\PostgreSQL\8.2\data\global"? Yes. > (b) Why is the pg_tablespace.spclocation column=null for pg_default & > pg_global? Because they're fixed by the base location of the cluster, they don't need to specified. > (c) When creating an object - if the tablespace attribute is not specified > - it is resolved as follows: > Index - Parent Table on which the index is being created. > Table - Tablespace of the database in which the table is being created. > Database - Tablespace of the template from which this database was created > (Ex: template1). > > If the above is the method by which the tablespace name is resolved, when > does the default_tablespace variable get used? > Or Will the above come into the picture if default_tablespace=NULL? It's the default if nothing has been specified. The docs say quite clearly that the default_tablespace GUC overrides those rules you gave. > (d) > The following objects are shared between all databases in a cluster. > pg.catalog.pg_group: List of user groups (View). > pg.catalog.pg_shadow: List of valid users. (View) > pg.catalog.pg_database: List of databases in the cluster. (Table) > pg.catalog.pg_tablespace: List of tablespaces. (Table) > > There are 33 tables & 33 Views in the pg_catalog schema. > Are there any other tables & views that are common to all databases in a > cluster? Anything in the pg_global tablespace. > Why have cluster common objects sitting in each database in the cluster? They only exist once, they are just visible from each database. > How can we differentiate between objects in pg_catalog? By OID? I'm not sure of the question. > Those that are specific to the database and those that are common to the > cluster? The stuff that's global is in pg_global, everything else is seperate. > (e) Can someone please point me to a document/URL that has information > about using temp tables in postgreSQL? > I have already read material that is available in the official PostgreSQL > documentation. > Want something more - the inner workings, pg_temp*, etc.. The docs cover all this, but you'll have to go to the section dealing with technical details. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Newbie Questions
On Fri, 2005-10-21 at 19:46 -0400, Douglas McNaught wrote: > Redefined Horizons <[EMAIL PROTECTED]> writes: > > > I'm running the latest stable version of PostgreSQL on a Debian Linux box > > running Gnome 2.0. I've just started setting up my first database with > > PostgreSQL and I've got a few newbie questions: > > I'm going to assume you installed the Debian packages rather than > installing from source... ...and if you did install from source, they're wherever you put them (as I frequently tell my daughter)... The real configuration files for the Debian packages are stored in /etc/postgresql and /etc/postgresql-common. There are symbolic links to those locations in the database directories. ... > > [2] Can I use the "SU" command to log in as Postgres if I am logged in as > > a non-root user, or is this only possible as the root user? > > If you give the postgres user a Unix password (as root) using the > 'passwd' command, you should be able to 'su' to that user from any > account. But generally that is thought to be inadvisable from a Unix security point of view, because it also allows anyone to log in as postgres without your knowing their real identity. You can use sudo to execute a command as another user, and then only your own password is needed. ... > > [4] How do I ensure that the Postmaster server process is started when I > > reboot my machine? > > It should be automatically started if you installed the Debian packages. If you didn't, look in contrib for an example of an init script. On Debian, this should be put into /etc/init.d and then you should use update-rc.d to set up the links for the runlevels in which you want it to run. > > [5] When I'm in PgAdmin III, does it matter if I add a new server as a > > non-root user, as the root-user, or as the Postgres user? What is the > > difference between the 3? > > Never used it, sorry. PgAdmin is a client application, so setting up a server means telling PgAdmin where to find the server you want to connect to. You should do it as the user who is going to be using PgAdmin. ... You do not generally need to use the postgres username; you can set up your own username as a PostgreSQL superuser and then you can do anything that postgres can do (except access the Unix files from outside a database connection). -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Newbie Questions
Changed your email format from HTML to plain text, which is preferred on most mailing lists. Redefined Horizons wrote: > I'm running the latest stable version of PostgreSQL on a Debian Linux > box running Gnome 2.0. I've just started setting up my first database > with PostgreSQL and I've got a few newbie questions: > > [1] Is there a way to determine where all the parts of my defualt > PostgreSQL installation are located? Unless you told it otherwise, it should all be installed in /usr/local/pgsql/bin. I don't run Debian, so I don't know if it changes things around. > > [2] Can I use the "SU" command to log in as Postgres if I am logged > in as a non-root user, or is this only possible as the root user? That's not really a PostgreSQL question, but rather a Unix question. The answer is yes, assuming you know the password for the Postgres userid. > > [3] How do I find the Postgres user's home directory, and what is > kept there? (This may be answered as a part of the response to > #1) Again, a Unix question. Look in /etc/passwd, the home directory is identified there. > > [4] How do I ensure that the Postmaster server process is started > when I reboot my machine? If you just want to see if it is running after the system is booted, then use "ps -ef | grep postmaster". If you want to configure your system to ensure that it will start up on reboot, then you need to add it to the init tasks. How that is done depends on your particular distribution, and again I don't know Debian. You may have a GUI to assist with that, but typically it amounts to including a symlink in /etc/rc3.d to /etc/init.d. I use Gentoo which is a whole different bowl of fish. > > [5] When I'm in PgAdmin III, does it matter if I add a new server as > a non-root user, as the root-user, or as the Postgres user? What is > the difference between the 3? You're not adding a new server to your PostgreSQL server, you are only adding a new list item to PgAdmin, pointing to a hopefully existing PostgreSQL server. This list will only be visible to the userid you used to create it. In other words, standard Unix conventions apply: each user has his/her own set of settings. > > [6] If I forgot the Postgres user password, do I have to reinstall > PostgreSQL to reset it, or can I do this somewhere else? If you leave everything installed via the defaults, you don't need any password to log in locally. So if you forget the password, you can log in locally with no password and reset it. > > Thanks for any help with these questions. > > Scott Huey -- Guy Rouillier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Newbie Questions
Redefined Horizons <[EMAIL PROTECTED]> writes: > I'm running the latest stable version of PostgreSQL on a Debian Linux box > running Gnome 2.0. I've just started setting up my first database with > PostgreSQL and I've got a few newbie questions: I'm going to assume you installed the Debian packages rather than installing from source... > [1] Is there a way to determine where all the parts of my defualt > PostgreSQL installation are located? $ dpkg -l "postgresql*" Then for each package listed, do $ dpkg -L > [2] Can I use the "SU" command to log in as Postgres if I am logged in as > a non-root user, or is this only possible as the root user? If you give the postgres user a Unix password (as root) using the 'passwd' command, you should be able to 'su' to that user from any account. > [3] How do I find the Postgres user's home directory, and what is kept > there? (This may be answered as a part of the response to #1) Look at the user's entry in '/etc/passwd'. > [4] How do I ensure that the Postmaster server process is started when I > reboot my machine? It should be automatically started if you installed the Debian packages. > [5] When I'm in PgAdmin III, does it matter if I add a new server as a > non-root user, as the root-user, or as the Postgres user? What is the > difference between the 3? Never used it, sorry. > [6] If I forgot the Postgres user password, do I have to reinstall > PostgreSQL to reset it, or can I do this somewhere else? If you're talking about the Unix password, root can change any password. If you're talking about the PG password, you can edit the ph_hba.conf file to allow 'postgres' to connect without a password, then go into 'psql' and change the password with ALTER USER. See the docs for more info on this part. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Newbie questions
From: Scott Alexander <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Tuesday, June 08, 1999 5:57 PM Subject: [GENERAL] Newbie questions >Database design - CASE tools > >For people who *don't* use a CASE tool: what do you find to be the most >efficient method of defining your database schema? The lack of user friendly interfaces is one of the reasons why I choose not to develop large (> 60 tables) databses in PostgreSQl...but when I do develop I simply save the SQL stmts in a text file so I can reuse them to recreate the database at any time. >Additional SQL features >=== >referential integrity, and automatic two-phase commit. (Borland's >Interbase 5.5 is a good example which incorporates all these features.) Ugg Interbase :) Lets not go there :) PostgreSQL supports Triggers, Stored Procedures, Rollback, and autocommit. Referential Integrity is not yet working. I leave it to others to itemize all the other nifty features :)
