[GENERAL] rotating log files
Hi What is a good solution for rotating postgresql log files? I'm running 'Linux 2.2.16-22smp #1 SMP i686 unknown' and a solution that was proposed to me was to use cron and a program called logrotate: DESCRIPTION logrotate is designed to ease administration of systems that generate large numbers of log files. It allows auto matic rotation, compression, removal, and mailing of log files. Each log file may be handled daily, weekly, monthly, or when it grows too large. Unfortunately, after replacing/rotating/compressing the database log file, the new log file remains empty. I have a feeling that postgres has lost the reference to the original log file and that the new log data is lost into the void. I'm curious what the consensus is about managing log files. Thanks Jelle Ouwerkerk Software Developer Openface Internet Inc. Montreal, Quebec, Canada http://www.openface.ca ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] starting personal postmaster
How can I start a personal postmaster, e.g. the postmaster should manage a database cluster in ~/pgdata/. I'm using the Debian package of postgresql (7.0.3) and it want's to create a socket at /var/run/postgres/.s.PORT.sock (or something similiar). As I am not user postgres I dont have the permission to do this. This seems to be the only problem to start postgres on an user account. Is this Debian specific? Are there methods to avoid this? I tried: $ mkdir ~/pgdata $ initdb -D ~/pgdata $ postmaster -D ~/pgdata Please CC answers to me [EMAIL PROTECTED]. Thanks, -- Stefan Karrmann ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] last comma inside CREATE TABLE () statements
Heh.. Actually, those queries look quite good if you centre them in a
page -- Assuming all characters are the same width anyway. SELECT,
FROM, and other key words go onto the left column along with comma's,
and the relevant database columns, tables, and where clauses go on the
right. With a good naming convention I don't even have to look at the
left hand side of the query but rather just the list of entities on
the right. It also means every line has a left side and a right side.
Anyway, not that it matters much but If the loose grammar is
implemented it should be optional and off by default.
--
Rod Taylor
There are always four sides to every story: your side, their side, the
truth, and what really happened.
- Original Message -
From: will trillich [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, April 27, 2001 12:56 AM
Subject: Re: [GENERAL] last comma inside CREATE TABLE () statements
On Thu, Apr 26, 2001 at 10:38:42AM -0400, Rod Taylor wrote:
Gah.. just put comma's at the beginning...
Oh, now THAT's intuitive:
. To be
, or not to be
, that is the question
. Whether 'tis nobler...
Charming. :)
SELECT bleah
, blah
, otherthing
FROM arghh
, feh
, fah
WHERE ( blah in ('1'
, '2'
, '3')
OR otherthing IS TRUE
)
OR bleah IS FALSE
Oh, and indent nicer. You can remove virtually any line (except
the
ones with commands in them) without any issues.
What that does, is it transfers the location of the problem. Now
the comma is effectively in FRONT of most terms, except the
FIRST.
An alternative compromise:
select
first
,
second
,
third
,
fourth
from
alpha
,
bravo
,
charlie
;
It's odd to use a whole line just for a florkin' comma, but in vi
2ddkkP or 2ddP will rearrange things nicely, while
keeping the purists at bay (not to mention any names, but You
Know Who You Are :).
I'd still prefer to ALLOW (but not DEMAND) 'empty after last
comma'. Or if you're determined to go for 'empty before first
comma':
update tbl
set
,one = something
,two = something-else
,three = fn('hgttg',42)
,four = that
;
But i hope you'll agree that this is more obtuse than we need to
be. Not to mention the speedbump effect it'll have on the person
who's got to look over your code next month.
This looks much nicer, imho --
update tbl
set
one = something ,
two = something-else ,
three = fn('hgttg',42) ,
four = that,
;
After all, the comma is of no importance to the conceptual task
we're after: i don't care if there's a token separating those
assignments -- i'm interested in the fields and the values being
assigned to them. The commas are just there to help us predict
that the compiler will understand what we're after.
And it's easy to rearrange those lines in a text editor without
having to be paranoid about Do i need to add a comma somewhere?
Should i look to see if i should take one out?
Computers should work. People should think. Data! Mow the lawn!
--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html
[EMAIL PROTECTED]
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
---(end of
broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [GENERAL] Re: Re: Need for newbie friendly docs (was Newbie struggling...)
Joel- In all fairness, there aren't any good HTML-based Unix tutorials. I've looked for them. In particular, Sun is worthless here, curious since their Java tutorial is (IMO) really well done. I'm hoping PostgreSQL is MORE stable than Oracle in our Solaris environment. The massive CPU/disk footprint of Oracle generates reliability errors in our databases which I hope can be avoided in a leaner package. This marvelous mailing list really gives me confidence. Clayton - Original Message - From: Joel Burton [EMAIL PROTECTED] To: Justin Clift [EMAIL PROTECTED] Cc: Oliver Elphick [EMAIL PROTECTED]; Geoff Caplan [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, April 26, 2001 1:53 PM Subject: [GENERAL] Re: Re: Need for newbie friendly docs (was Newbie struggling...) On Fri, 27 Apr 2001, Justin Clift wrote: Newbies have interesting ideas sometimes too. After all, they've spent their time learning about something OTHER than Unix. :-) something... other... than... unix ? Justin, I'm not clear on what you mean. Can you give us an example? ;-) But really: sure! Oracle, ferinstance, realizes that many people run Unix *because* they want to run Oracle in a stable server environment. People may be making the same decision about PostgreSQL. We shouldn't have to write this, though... if people could contribute the great 'basics of Unix you need to know to be a decent DBA' stuff that's already on the web, we'd have plenty. -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] JDBC and Accents
On Fri, Apr 27, 2001 at 02:36:21PM +0200, Loïc Courtois wrote: On Fri, Apr 27, 2001 at 01:56:38AM +0200, Loïc Courtois wrote: Hello, I have some problems to display the accents in my db, using the JDBC and postgres 7.1. Apparently, all accents are replaced by a '?'. What is your database encoding? You can insert whatever stuff you want into db and psql displays it fine - it does not care what it is. My database encoding is SQL_ASCII... Is there any temporary solutions, or may a convert the db in an other encoding? Correct solutions is really dump database, create db with right encoding and restore. But for temporary solution ;) you could use PostgreSQL 7.0 JDBC driver, which does not support those db encodings... -- marko ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Returning tuple(s) from C-Functions
Hi. I'm into programming a function for pg for searching a ldap directory. But this whole effort has absolutely no sense, if i don't find a way to return _at least_ one whole tuple from a C-Function, e.g. create function foobar(footable) returns bartable as '/var/lib/pgsql/data/foobar.o' language 'c' ; and i return a pointer to a tuple or something? This is what the FAQ says about it: 5.3) How do I write a C function to return a tuple? This requires wizardry so extreme that the authors have never tried it, though in principle it can be done? Is this answer still correct? Is there a way to do it with 7.1? Can i write to a temporary table in this function and insert into the backend somehow? I'm not expecting too much people having this done, but if anyone did it, please reply... tia, Philip -- | | Philip Poten|\ \ | | | | | | \ \/ / YL | | Unix Operator \ \| | | | | | \ \/ i | |_+43/1/91 999 - 207 | | |_| | /\ \ n |[EMAIL PROTECTED]/ / /\ \ e ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] caching/ virtual file systems
A newbie question as I prepare to dive into this: Under $PGDATA, can I expect to see subdirectories emerge within databases, say for INDICES, TABLES, etc? The reason: On Solaris, I have some RAM in /tmp I want to employ as a virtual file system to increase the performance of PG. I've mounted the software in /tmp/pgsql/,and I would also like to mount some of the data files here as well. SincePG will create files I will need for the symbolic links to be at the directory level, so I was wondering if anyone haddeveloped any "tricks" to get certain blocks of data files to fall into subdirectories?
[GENERAL] Re: I am now Linux and PostgreSQL user, have a question
On Fri, 27 Apr 2001, Isiah Thomas wrote: I read some docunment say that to start the postgres service must type this command /etc/rc.d/init.d/postgres start That's pretty specific to RedHat and RedHat-derived systems. (And then, only if you install PostgreSQL from the RPMs, which if you're using RedHat, you probably are). A less distribution-specific way of saying the same thing is $ service postgres start But, really, you probably want to do something like $ pg_ctl -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SQL Where LIKE - Range it!
select * from table where last_name ~ '^[A-F]'; or select * from table where last_name between 'A' and 'G'; or select * from table where last_name ='A' and last_name'G' The second one is broken if last_name='G' returns something. Use ~* in first example to ignore case. Frank At 08:16 PM 4/26/01 GMT, you wrote: H- I've found the docs on how to select a list of rows from a table were all the records have a last name starting with 'W%'. select * from table where last_name LIKE 'W%' What I'd like to do is pull a list of records where there is a range of last names; say from A - F. select * from table where last_name LIKE 'A%' AND last_name LIKE 'F%' - for example. The above code I've tried for this doesn't seem to work as I'd expect it too? I've even done select * from table where last_name LIKE 'A%' AND LIKE 'F%' Can anyone provide some details or insights on how to accomplish this? Thanks. Much appreciated. -Steagus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Re: Report Writer for PostgreSQL
From: Patrick Lanphier [EMAIL PROTECTED] snip Well it needs the capability format data on many different graph, capable of generating HTML, PDF, and RTF formats. The server will be running on Linux but the design platform can be whatever. The problem I had with one report writer was the data from the database was present one way and it was not capable on rotating the data for the graph and I wasn't about to do this for the report writer. Is there somebody I should contact that you know about a report writer? There is one product that might be of interest, unlike many GUI report generators it does not restrict the types of reports you make, it's called the Practical Extraction and Report Language oh yeah and it's free. nb. i'm a python guy but i couldn't reist ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: SQL Where LIKE - Range it!
On Thu, 26 Apr 2001, Steagus wrote: What I'd like to do is pull a list of records where there is a range of last names; say from A - F. select * from table where last_name LIKE 'A%' AND last_name LIKE 'F%' - for example. The above code I've tried for this doesn't seem to work as I'd expect it too? I've even done select * from table where last_name LIKE 'A%' AND LIKE 'F%' Can anyone provide some details or insights on how to accomplish this? LIKE A% AND LIKE F% means must start with A *AND* must start with F, so the name Anderson would fail because it does start with A, but doesn't start with F. Something like LIKE A% OR LIKE B% OR LIKE C% ... OR LIKE F% would do the trick, but slowly, and it's a pain to write out. I'd use BETWEEN 'A' AND 'FZZZ' (or, to be more precise, ='A' and 'G') Keep in mind that PostgreSQL is case-sensitive, so if me name were 'Joel deBurton', you wouldn't find me. If you have lower-case starting names, you'll want to see (BETWEEN 'A' AND 'FZZZ') OR (BETWEEN 'a' AND 'fzzz') HTH, -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: SQL Where LIKE - Range it!
What I'd like to do is pull a list of records where there is a range of last names; say from A - F. select * from table where last_name LIKE 'A%' AND last_name LIKE 'F%' - for example. The above code I've tried for this doesn't seem to work as I'd expect it too? When you use the AND boolean operator, you are asking for records that match BOTH boolean expressions. And I don't know many words that start with A *and* F. :) You want to use the OR operator: SELECT * FROM table_name WHERE last_name LIKE 'A%' OR last_name LIKE 'F%' Can anyone provide some details or insights on how to accomplish this? If you want a range, you'll have to use a regular expression (or a whole bunch of LIKE expressions for every value in the range. A regular expression version would be: SELECT * FROM table_name WHERE last_name ~ '^[A-F]' The tilde (~) tells it to match on a regular expression, the carat (^) tells it to match the beginning of the string, the brackets match a single character, and the A-F matches one letter in that range. Hope this helps! Greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] starting personal postmaster
Stefan Karrmann writes: How can I start a personal postmaster, e.g. the postmaster should manage a database cluster in ~/pgdata/. I'm using the Debian package of postgresql (7.0.3) and it want's to create a socket at /var/run/postgres/.s.PORT.sock (or something similiar). As I am not user postgres I dont have the permission to do this. This seems to be the only problem to start postgres on an user account. Is this Debian specific? Are there methods to avoid this? Yes. Uninstall the package, install from source. Alternative 1: Change the permissions on /var/run/postgres. Alternative 2: Use the postmaster -k option, but your client programs are not going to see that. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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
[GENERAL] rotating log files
I think I may have found an answer to my own problem:
What is a good solution for rotating postgresql log files?
I'm running 'Linux 2.2.16-22smp #1 SMP i686 unknown' and a solution that
was proposed to me was to use cron and a program called logrotate:
Unfortunately, after replacing/rotating/compressing the database log
file, the new log file remains empty. I have a feeling that postgres has
lost the reference to the original log file and that the new log data is
lost into the void.
The (new) contents of /etc/logrotate.d/postgres:
# start
/var/log/postgresql.log {
compress
rotate 10
create 0664 postgres postgres
size=1k
weekly
sharedscripts
postrotate
/etc/rc.d/init.d/postgresql restart
endscript
}
#end
From the logrotate man page:
size size
Log files are rotated when they grow bigger then
size bytes.
weekly
Log files are rotated if the current weekday is
less then the weekday of the last rotation or if
more then a week has passed since the last rota
tion.
postrotate/endscript
The lines between postrotate and endscript (both of
which must appear on lines by themselves) are exe
cuted after the log file is rotated. These direc
tives may only appear inside of a log file defini
tion. See prerotate as well.
sharedscripts
Normally, prescript and postscript scripts are run
for each log which is rotated, meaning that a sin
gle script may be run multiple times for log file
entries which match multiple files (such as the
/var/log/news/* example). If sharedscript is speci
fied, the scripts are only run once, no matter how
many logs match the wildcarded pattern. A side
effect of this option is that the scripts are
always executed, even if no logs are rotated (if it
is not specified, the scripts are run only if logs
are actually rotated) (this overrides the noshared
scripts option).
Any comments about this setup are welcome. :)
Jelle Ouwerkerk
Software Developer
Openface Internet Inc.
Montreal, Quebec, Canada
http://www.openface.ca
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] SQL Where Like - Range it?!
What I'd like to do is pull a list of records where there is a range of last names; say from A - F. select * from table where last_name LIKE 'A%' AND last_name LIKE 'F%' - for example. The above code I've tried for this doesn't seem to work as I'd expect it too? SELECT * FROM table WHERE last_name BETWEEN 'A' AND 'Fz' ; worked for me. You could also use BETWEEN 'A' AND 'G' to avoid all of the s at the end. Crude but effective. len morgan ---(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: [GENERAL] rotating log files
Jelle Ouwerkerk writes: I'm running 'Linux 2.2.16-22smp #1 SMP i686 unknown' and a solution that was proposed to me was to use cron and a program called logrotate: Unfortunately, after replacing/rotating/compressing the database log file, the new log file remains empty. I have a feeling that postgres has lost the reference to the original log file and that the new log data is lost into the void. The logrotate program just moves the log file to a different name, but the PostgreSQL server has the file open so it just keeps writing to the same file no matter what name it has. What you need to do is pipe the log output through a separate program that closes and reopens the file once in a while. Apache has such a program, for example. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] creating constants in postgres
Thomas F. O'Connell writes: is there in postgres a way to create a constant like CURRENT_DATE for general use? Those things are just functions with a special syntax. No, it's not easily possible to create more such functions, but it's easy to create regular functions. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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: [GENERAL] caching/ virtual file systems
Clayton Vernon writes: A newbie question as I prepare to dive into this: Under $PGDATA, can I expect to see subdirectories emerge within databases, say for INDICES, TABLES, etc? The layout (in 7.1) is $PGDATA/base/oid of database/oid of table,index... The reason: On Solaris, I have some RAM in /tmp I want to employ as a virtual file system to increase the performance of PG. I've mounted the software in /tmp/pgsql/, and I would also like to mount some of the data files here as well. Since PG will create files I will need for the symbolic links to be at the directory level, so I was wondering if anyone had developed any tricks to get certain blocks of data files to fall into subdirectories? Unless you like your data, don't do that. What happens in case of a crash? -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Re: I am now Linux and PostgreSQL user, have a question
Joel Burton wrote: On Fri, 27 Apr 2001, Isiah Thomas wrote: I read some docunment say that to start the postgres service must type this command /etc/rc.d/init.d/postgres start That's pretty specific to RedHat and RedHat-derived systems. (And then, only if you install PostgreSQL from the RPMs, which if you're using RedHat, you probably are). Plus, the file is /etc/rc.d/init.d/postgresql under RedHat (at least from the rpm-build.) -- Steve Wampler- SOLIS Project, National Solar Observatory [EMAIL PROTECTED] ---(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
[GENERAL] Re: running pgaccess on localhost
On Fri, 27 Apr 2001, Mike Goetz wrote: Hello, pgsql newbie here... I'm trying to run pgaccess on a database I've created, and I get the following error: PostgreSQL error message: Connection to database failed PQconnectPoll() -- connect() failed: Connection refused Is the postmaster running (with -i) at 'localhost' and accepting connections on TCP/IP port '5432'? Now the postmaster is not normally set up with the -i option, and I'd like to keep it that way since I'm intending to use my workstation for local development only (are there any security issues by activating the -i option?). Indeed turning the -i option on does in fact allow me to use pgaccess without error. However, since I'm running pgaccess from a terminal window on my machine I would have thought that this would constitute a local domain socket connection (or at least a connection from the localhost), so I'm confused by the error message -- am I missing something or do I really have to enable TCP/IP connections if I want to initiate database access using pgacess from a terminal window? The fact that you're running pgaccess from a term window doesn't mean that pgaccess connects to PG w/a local socket. Someone may know how to configure pgaccess to use a local socket. If not, use the -i switch, and edit $PGDATA/pg_hba.conf to block access from other machines. (This is the default setting.) -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] General usage for pg_dumpall
Hi, I want to backup a database. I've read here that pg_dumpall is really the best way to do it. However, what options are going to be most useful to me, should I need to restore the db ever. I know there's probably lots of conditional things, but just in general, what are the most commonly used. I'm mainly looking at the benefit of using -o and -d. Thanks, 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
Re: [GENERAL] rotating log files
# start
/var/log/postgresql.log {
compress
rotate 10
create 0664 postgres postgres
size=1k
weekly
sharedscripts
postrotate
/etc/rc.d/init.d/postgresql restart
endscript
}
Alternatively, instead of restarting postgres, the copytruncate option can
be used to truncate the log file in place, allowing postgres to continue
writting to the file without needing to restart.
Sincerely, Warren
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] pg_encoding
Hi folks, I'm upgrading from Postgres 6.5 to 7.1. 6.5 was originally installed using apt-get (this is on a debian potato box). I'm installing 7.1 from tarball, because there are some particular things I want. Anyway, I'm dumping my database, and I get the command: pg_encoding: command not found. The whole thing appears dumped, and the file looks OK. I've done dumps on individual databases before, and gotten the same error, and I've been able to restore those databases without too much trouble. Is there something I'm missing and should watch out for - or should be worried about? Michelle -- Michelle Murrain, Ph.D. President Norwottuck Technology Resources [EMAIL PROTECTED] http://www.norwottuck.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] ON DELETE Behavior after the fact
I have a database in production that requires some ON DELETE behavior. Is there a way to use ALTER TABLE to define ON DELETE behavior for the foreign keys, or will I have to write triggers at this point? ___ John Pagakis DevelopOnline.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] PostgreSQL and mySQL database size question
To all,
Before I begin, this is NOT a PostgreSQL verses mySQL post. I have used
both databases and have found both of these products to be excellent choices
depending on the specific requirements for a given project. That said, I
am trying to understand the database size differences between PostgreSQL and
mySQL for a current project I am working on. I have attached the schemas
for the PostgreSQL and mySQL databases below (minus three tables that are
not important for this discussion and have no data in them).
I created the database as defined in the schemas below in both PostgreSQL
v7.1 (on Windows 2000 via cygwin) and mySQL 3.23.37 (on Windows 2000 native)
and populated them both with the same data: PIN table with 1,740 tuples and
the PINNDX table with 92,488 tuples. Looking at the number of files and
size of the files, I get the following (after doing a vacuumdb with
PostgreSQL):
PostgreSQL
Files 109
Size 60.7MB
mySQL
Files 24 (3 x 8 tables)
Size 11.1MB
Why is there such a discrepancy in the database sizes? I know that
PostgreSQL has additional capabilities such as logging however, without
being able to ascribe a function to each of the numbered files in the
PostgreSQL database, it is unclear to me what is taking up so much space.
Performance and capabilities (and the cost of disk space) aside, this is a
major problem for me. If anyone can shed some light on this I would
appreciate it.
Thanks, Fred
PostgreSQL schema
CREATE SEQUENCE pins_seq;
CREATE TABLE pins (
pinnumINTEGER NOT NULL DEFAULT nextval('pins_seq') PRIMARY KEY,
modified TIMESTAMP NOT NULL DEFAULT now(),
indexed CHAR(1) NOT NULL DEFAULT 'N',
sindexed CHAR(1) NOT NULL DEFAULT 'N',
pin TEXT NOT NULL
)
;
CREATE INDEX indexed_ndx ON pins (indexed);
CREATE INDEX sindexed_ndx ON pins (sindexed);
CREATE SEQUENCE pinndx_seq;
CREATE TABLE pinndx (
pinndxnum INTEGER NOT NULL DEFAULT nextval('pinndx_seq') PRIMARY KEY,
parentINTEGER NOT NULL,
tagpath INTEGER NOT NULL,
tagtype CHAR(1) NOT NULL,
tagname INTEGER NOT NULL,
atrname INTEGER NOT NULL,
pinnumINTEGER NOT NULL,
nvalueFLOAT4,
value TEXT
)
;
CREATE INDEX parent_ndx ON pinndx (parent);
CREATE INDEX tagpath_ndx ON pinndx (tagpath);
CREATE INDEX tagname_ndx ON pinndx (tagname);
CREATE INDEX atrname_ndx ON pinndx (atrname);
CREATE INDEX pinnum_ndx ON pinndx (pinnum);
CREATE INDEX nvalue_ndx ON pinndx (nvalue);
CREATE INDEX value_ndx ON pinndx (value);
CREATE SEQUENCE tagpath_seq;
CREATE TABLE tagpathtbl (
vkey INTEGER NOT NULL DEFAULT nextval('tagpath_seq') PRIMARY KEY,
value VARCHAR(255) NOT NULL
);
CREATE INDEX tagpathtbl_ndx ON tagpathtbl (value);
CREATE SEQUENCE tagname_seq;
CREATE TABLE tagnametbl (
vkey INTEGER NOT NULL DEFAULT nextval('tagname_seq') PRIMARY KEY,
value VARCHAR(255) NOT NULL
);
CREATE INDEX tagnametbl_ndx ON tagnametbl (value);
CREATE SEQUENCE atrname_seq;
CREATE TABLE atrnametbl (
vkey INTEGER NOT NULL DEFAULT nextval('atrname_seq') PRIMARY KEY,
value VARCHAR(255) NOT NULL
);
CREATE INDEX atrnametbl_ndx ON atrnametbl (value);
mySQL schema
CREATE TABLE pins (
pinnumINTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME NOT NULL,
indexed CHAR(1) NOT NULL DEFAULT 'N',
INDEX indexedndx (indexed),
sindexed CHAR(1) NOT NULL DEFAULT 'N',
INDEX sindexedndx (sindexed),
pin MEDIUMTEXT NOT NULL
)
AVG_ROW_LENGTH=5000
MAX_ROWS=31536
PACK_KEYS=1
ROW_FORMAT=compressed
;
CREATE TABLE pinndx (
pinndxnum INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
parentINTEGER UNSIGNED NOT NULL,
INDEX parentndx (parent),
tagpath INTEGER UNSIGNED NOT NULL,
INDEX tagpathndx (tagpath),
tagtype CHAR(1) NOT NULL,
tagname INTEGER UNSIGNED NOT NULL,
INDEX tagnamendx (tagname),
atrname INTEGER UNSIGNED NOT NULL,
INDEX atrnamendx (atrname),
pinnumINTEGER UNSIGNED NOT NULL,
INDEX pinnumndx (pinnum),
nvalueDOUBLE,
INDEX nvaluendx (nvalue),
value MEDIUMTEXT,
INDEX valuendx (value(30))
)
AVG_ROW_LENGTH=500
MAX_ROWS=315360
PACK_KEYS=1
;
CREATE TABLE tagpathtbl (
vkey INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
value VARCHAR(255) NOT NULL,
INDEX tagpathndx (value(30))
);
CREATE TABLE tagnametbl (
vkey INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
value VARCHAR(255) NOT NULL,
INDEX tagnamendx (value(30))
);
CREATE TABLE atrnametbl (
vkey INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
value VARCHAR(255) NOT NULL,
INDEX atrnamendx (value(30))
);
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [GENERAL] PostgreSQL and mySQL database size question
Bruce,
I have your book right in front of me... do you have that information in the
book? If not, I will take a look at the FAQ. As for mySQL, here are the
storage requirements:
Storage requirements for numeric types
Column type Storage required
TINYINT 1 byte
SMALLINT2 bytes
MEDIUMINT 3 bytes
INT 4 bytes
INTEGER 4 bytes
BIGINT 8 bytes
FLOAT(X)4 if X = 24 or 8 if 25 = X = 53
FLOAT 4 bytes
DOUBLE 8 bytes
DOUBLE PRECISION8 bytes
REAL8 bytes
DECIMAL(M,D)M+2 bytes if D 0, M+1 bytes if D = 0 (D+2, if M D)
NUMERIC(M,D)M+2 bytes if D 0, M+1 bytes if D = 0 (D+2, if M D)
Storage requirements for date and time types
Column type Storage required
DATE3 bytes
DATETIME8 bytes
TIMESTAMP 4 bytes
TIME3 bytes
YEAR1 byte
Storage requirements for string types
Column type Storage required
CHAR(M) M bytes, 1 = M = 255
VARCHAR(M) L+1 bytes, where L = M and 1 = M = 255
TINYBLOB,
TINYTEXTL+1 bytes, where L 2^8
BLOB, TEXT L+2 bytes, where L 2^16
MEDIUMBLOB,
MEDIUMTEXT L+3 bytes, where L 2^24
LONGBLOB,
LONGTEXTL+4 bytes, where L 2^32
ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of
enumeration values (65535 values maximum)
SET('value1','value2',...) 1, 2, 3, 4 or 8 bytes, depending on the number of
set members (64 members maximum)
Fred
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 27, 2001 1:48 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] PostgreSQL and mySQL database size question
Did you see the FAQ items on estimating database sizes? Does MySQL have
less overhead per row?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] ON DELETE Behavior after the fact
On Fri, 27 Apr 2001, John Pagakis wrote: I have a database in production that requires some ON DELETE behavior. Is there a way to use ALTER TABLE to define ON DELETE behavior for the foreign keys, or will I have to write triggers at this point? The easiest thing is probably to drop the triggers for the foreign key constraint you want to change, and re-add the constraint using ALTER TABLE specifying the on delete behavior. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL and mySQL database size question
Did you see the FAQ items on estimating database sizes? Does MySQL have
less overhead per row?
[ Charset ISO-8859-1 unsupported, converting... ]
To all,
Before I begin, this is NOT a PostgreSQL verses mySQL post. I have used
both databases and have found both of these products to be excellent choices
depending on the specific requirements for a given project. That said, I
am trying to understand the database size differences between PostgreSQL and
mySQL for a current project I am working on. I have attached the schemas
for the PostgreSQL and mySQL databases below (minus three tables that are
not important for this discussion and have no data in them).
I created the database as defined in the schemas below in both PostgreSQL
v7.1 (on Windows 2000 via cygwin) and mySQL 3.23.37 (on Windows 2000 native)
and populated them both with the same data: PIN table with 1,740 tuples and
the PINNDX table with 92,488 tuples. Looking at the number of files and
size of the files, I get the following (after doing a vacuumdb with
PostgreSQL):
PostgreSQL
Files 109
Size 60.7MB
mySQL
Files 24 (3 x 8 tables)
Size 11.1MB
Why is there such a discrepancy in the database sizes? I know that
PostgreSQL has additional capabilities such as logging however, without
being able to ascribe a function to each of the numbered files in the
PostgreSQL database, it is unclear to me what is taking up so much space.
Performance and capabilities (and the cost of disk space) aside, this is a
major problem for me. If anyone can shed some light on this I would
appreciate it.
Thanks, Fred
PostgreSQL schema
CREATE SEQUENCE pins_seq;
CREATE TABLE pins (
pinnumINTEGER NOT NULL DEFAULT nextval('pins_seq') PRIMARY KEY,
modified TIMESTAMP NOT NULL DEFAULT now(),
indexed CHAR(1) NOT NULL DEFAULT 'N',
sindexed CHAR(1) NOT NULL DEFAULT 'N',
pin TEXT NOT NULL
)
;
CREATE INDEX indexed_ndx ON pins (indexed);
CREATE INDEX sindexed_ndx ON pins (sindexed);
CREATE SEQUENCE pinndx_seq;
CREATE TABLE pinndx (
pinndxnum INTEGER NOT NULL DEFAULT nextval('pinndx_seq') PRIMARY KEY,
parentINTEGER NOT NULL,
tagpath INTEGER NOT NULL,
tagtype CHAR(1) NOT NULL,
tagname INTEGER NOT NULL,
atrname INTEGER NOT NULL,
pinnumINTEGER NOT NULL,
nvalueFLOAT4,
value TEXT
)
;
CREATE INDEX parent_ndx ON pinndx (parent);
CREATE INDEX tagpath_ndx ON pinndx (tagpath);
CREATE INDEX tagname_ndx ON pinndx (tagname);
CREATE INDEX atrname_ndx ON pinndx (atrname);
CREATE INDEX pinnum_ndx ON pinndx (pinnum);
CREATE INDEX nvalue_ndx ON pinndx (nvalue);
CREATE INDEX value_ndx ON pinndx (value);
CREATE SEQUENCE tagpath_seq;
CREATE TABLE tagpathtbl (
vkey INTEGER NOT NULL DEFAULT nextval('tagpath_seq') PRIMARY KEY,
value VARCHAR(255) NOT NULL
);
CREATE INDEX tagpathtbl_ndx ON tagpathtbl (value);
CREATE SEQUENCE tagname_seq;
CREATE TABLE tagnametbl (
vkey INTEGER NOT NULL DEFAULT nextval('tagname_seq') PRIMARY KEY,
value VARCHAR(255) NOT NULL
);
CREATE INDEX tagnametbl_ndx ON tagnametbl (value);
CREATE SEQUENCE atrname_seq;
CREATE TABLE atrnametbl (
vkey INTEGER NOT NULL DEFAULT nextval('atrname_seq') PRIMARY KEY,
value VARCHAR(255) NOT NULL
);
CREATE INDEX atrnametbl_ndx ON atrnametbl (value);
mySQL schema
CREATE TABLE pins (
pinnumINTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME NOT NULL,
indexed CHAR(1) NOT NULL DEFAULT 'N',
INDEX indexedndx (indexed),
sindexed CHAR(1) NOT NULL DEFAULT 'N',
INDEX sindexedndx (sindexed),
pin MEDIUMTEXT NOT NULL
)
AVG_ROW_LENGTH=5000
MAX_ROWS=31536
PACK_KEYS=1
ROW_FORMAT=compressed
;
CREATE TABLE pinndx (
pinndxnum INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
parentINTEGER UNSIGNED NOT NULL,
INDEX parentndx (parent),
tagpath INTEGER UNSIGNED NOT NULL,
INDEX tagpathndx (tagpath),
tagtype CHAR(1) NOT NULL,
tagname INTEGER UNSIGNED NOT NULL,
INDEX tagnamendx (tagname),
atrname INTEGER UNSIGNED NOT NULL,
INDEX atrnamendx (atrname),
pinnumINTEGER UNSIGNED NOT NULL,
INDEX pinnumndx (pinnum),
nvalueDOUBLE,
INDEX nvaluendx (nvalue),
value MEDIUMTEXT,
INDEX valuendx (value(30))
)
AVG_ROW_LENGTH=500
MAX_ROWS=315360
PACK_KEYS=1
;
CREATE TABLE tagpathtbl (
vkey INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
value VARCHAR(255) NOT NULL,
INDEX tagpathndx (value(30))
);
CREATE TABLE tagnametbl (
vkey INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
value VARCHAR(255) NOT NULL,
INDEX tagnamendx (value(30))
);
CREATE TABLE atrnametbl (
vkey INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
value VARCHAR(255) NOT NULL,
INDEX atrnamendx (value(30))
);
Re: [GENERAL] PostgreSQL and mySQL database size question
FAQ item 4.7 discusses table size computations. My guess is that it is
the 36 bytes-per-row that is the problem. Those bytes record the
visibility of the row for proper transactions semantics and MVCC.
Bruce,
I have your book right in front of me... do you have that information in the
book? If not, I will take a look at the FAQ. As for mySQL, here are the
storage requirements:
Storage requirements for numeric types
Column type Storage required
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT 4 bytes
INTEGER 4 bytes
BIGINT8 bytes
FLOAT(X) 4 if X = 24 or 8 if 25 = X = 53
FLOAT 4 bytes
DOUBLE8 bytes
DOUBLE PRECISION 8 bytes
REAL 8 bytes
DECIMAL(M,D) M+2 bytes if D 0, M+1 bytes if D = 0 (D+2, if M D)
NUMERIC(M,D) M+2 bytes if D 0, M+1 bytes if D = 0 (D+2, if M D)
Storage requirements for date and time types
Column type Storage required
DATE 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
TIME 3 bytes
YEAR 1 byte
Storage requirements for string types
Column type Storage required
CHAR(M) M bytes, 1 = M = 255
VARCHAR(M)L+1 bytes, where L = M and 1 = M = 255
TINYBLOB,
TINYTEXT L+1 bytes, where L 2^8
BLOB, TEXTL+2 bytes, where L 2^16
MEDIUMBLOB,
MEDIUMTEXTL+3 bytes, where L 2^24
LONGBLOB,
LONGTEXT L+4 bytes, where L 2^32
ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of
enumeration values (65535 values maximum)
SET('value1','value2',...) 1, 2, 3, 4 or 8 bytes, depending on the number of
set members (64 members maximum)
Fred
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 27, 2001 1:48 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] PostgreSQL and mySQL database size question
Did you see the FAQ items on estimating database sizes? Does MySQL have
less overhead per row?
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
RE: [GENERAL] PostgreSQL and mySQL database size question
OK, but that only accounts for 3.2MB of the extra 49.6MB used by PostgreSQL.
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 27, 2001 2:05 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] PostgreSQL and mySQL database size question
FAQ item 4.7 discusses table size computations. My guess is that it is
the 36 bytes-per-row that is the problem. Those bytes record the
visibility of the row for proper transactions semantics and MVCC.
Bruce,
I have your book right in front of me... do you have that information in
the
book? If not, I will take a look at the FAQ. As for mySQL, here are the
storage requirements:
Storage requirements for numeric types
Column type Storage required
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT 4 bytes
INTEGER 4 bytes
BIGINT8 bytes
FLOAT(X) 4 if X = 24 or 8 if 25 = X = 53
FLOAT 4 bytes
DOUBLE8 bytes
DOUBLE PRECISION 8 bytes
REAL 8 bytes
DECIMAL(M,D) M+2 bytes if D 0, M+1 bytes if D = 0 (D+2, if M D)
NUMERIC(M,D) M+2 bytes if D 0, M+1 bytes if D = 0 (D+2, if M D)
Storage requirements for date and time types
Column type Storage required
DATE 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
TIME 3 bytes
YEAR 1 byte
Storage requirements for string types
Column type Storage required
CHAR(M) M bytes, 1 = M = 255
VARCHAR(M)L+1 bytes, where L = M and 1 = M = 255
TINYBLOB,
TINYTEXT L+1 bytes, where L 2^8
BLOB, TEXTL+2 bytes, where L 2^16
MEDIUMBLOB,
MEDIUMTEXTL+3 bytes, where L 2^24
LONGBLOB,
LONGTEXT L+4 bytes, where L 2^32
ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of
enumeration values (65535 values maximum)
SET('value1','value2',...) 1, 2, 3, 4 or 8 bytes, depending on the number
of
set members (64 members maximum)
Fred
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 27, 2001 1:48 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] PostgreSQL and mySQL database size question
Did you see the FAQ items on estimating database sizes? Does MySQL have
less overhead per row?
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] WAL Log using all my disk space!
Hi all, The problem: I do a large bulk copy once a day (100,000 records of Radius data), tearing down indices, truncating a large table that contains summary information, and rebuilding everything after the copy. Over the course of this operation, I can generate up to 1.5 gigs of WAL data in pg_xlog. Sometimes (like just now), I will run out of disk space and the postmaster will crash. I try to restart it, and it errors out. Then I delete all the WAL logs, try to restart, and (surprise) it errors out again. I tried to set some of the of the WAL parameters in postgres.conf like so: wal_buffers = 4 # min 4 wal_files = 8 # range 0-64 wal_sync_method = fdatasync # fsync or fdatasync or open_sync or open_datasync but I get 24+ separate files. I would like to recover without an initdb, but if that isn't possible, I would definitely like to avoid this problem in the future. Thanks to all ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Help, I dropped a system datatype, and now I'm ....
Hi, I inadvertantly dropped a system data type (box) and now I am getting errors cannot find datatype oid 603 when I do some selects I have two possible solutions; 1 of which doesn't work yet insert back into the pg_type table the box row copied from another machine with oid 603, which postgres doesn't allow me to do. recreate the type and find all the references to it. Can someone tell me where to look for all the references to it? Thanks in advance, Dave Cramer ---(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
