[ADMIN] Problems with dump

[Ricardo Valença de Assis]

Hello World!
 
    I´m trying to migrate a database 
from one server to another but I got a hard problem. All the tables 
and sequences are migrated but the field last_value of ALL sequences is set to 
1. Why this? The others fields of them are set correctly but last_value it not. 
Is there a way to dump only sequences?
 
Thanks!
 
Att.Ricardo ValençaUEAD - 
UGF

[ADMIN] newbie : START TRANSACTION

[Zouari Fourat]

Hello
am new to transactional sql (just moved from mysql) and would like to
use transactions and i want to ask about it here, when am in a
transaction block can i execute other queries on the server (that
table exactly) or shoudl i commit the transaction to free the table ?

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] newbie : START TRANSACTION

[Scott Marlowe]

On Tue, 2005-02-15 at 09:14, Zouari Fourat wrote:
> Hello
> am new to transactional sql (just moved from mysql) and would like to
> use transactions and i want to ask about it here, when am in a
> transaction block can i execute other queries on the server (that
> table exactly) or shoudl i commit the transaction to free the table ?

Firstly, read through this section of the manual:

http://www.postgresql.org/docs/8.0/static/mvcc.html

Secondly, postresql is designed around transactions, and generally you
don't have to worry about accidentally doing too much in one or anything
like that.  This is especially true of DML (Data manipulation language)
queries.

However, transactions and DDL (data definition language) are a slightly
different story.  While everything except create / drop database is
transactable, DDL statements tend to take out locks that do lock out
other users while running, and therefore should be run as a batch then
committed or not quickly, generally speaking.

Basically, as long as you're updating your data, you don't have to worry
too much about what's been locked, except for some visibility issues
(you might need select or update for some of those problems, as well as
serializable mode).  When you're alter table structure and such, then it
can become an issue to other usres.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[ADMIN] empty a database

[Dick Davies]

Is there a neat way to clean out a database via SQL commands?

i.e. get rid of tables, sequences, integers, etc.

At present I'm using dropdb/createdb, but thats' far from ideal
and I think it's causing postgres to do more mork than it needs to...

-- 
'My life, and by extension everyone else's, is meaningless.'
-- Bender
Rasputin :: Jack of All Trades - Master of Nuns

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] empty a database

[Tom Lane]

Dick Davies <[EMAIL PROTECTED]> writes:
> Is there a neat way to clean out a database via SQL commands?
> i.e. get rid of tables, sequences, integers, etc.

> At present I'm using dropdb/createdb, but thats' far from ideal
> and I think it's causing postgres to do more mork than it needs to...

Well, if you put everything into one or a few schemas then dropping
and recreating those schemas would do it.  I suspect though that this
is *not* faster than dropdb/createdb.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] empty a database

[Dick Davies]

* Tom Lane <[EMAIL PROTECTED]> [0232 16:32]:
> Dick Davies <[EMAIL PROTECTED]> writes:
> > Is there a neat way to clean out a database via SQL commands?
> > i.e. get rid of tables, sequences, integers, etc.
> 
> > At present I'm using dropdb/createdb, but thats' far from ideal
> > and I think it's causing postgres to do more mork than it needs to...
> 
> Well, if you put everything into one or a few schemas then dropping
> and recreating those schemas would do it.  I suspect though that this
> is *not* faster than dropdb/createdb.

Thanks Tom.

It's not just the speed, it's the constant deletes and creations in
~pgsql/data - as I said the other day, this is recreating a test db from the
production one as part of unit tests, so this happens dozens of times a day...


-- 
'...and then we wrote scripts to write the configs for us, and using
these scripts, we made mistakes in a faster, more automated manner.'
-- A Gentle Introduction to Cricket, on MRTG configuration
Rasputin :: Jack of All Trades - Master of Nuns

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] empty a database

[Naomi Walker]

How about just truncating all the tables?


Dick Davies wrote:
> * Tom Lane <[EMAIL PROTECTED]> [0232 16:32]:
> 
>>Dick Davies <[EMAIL PROTECTED]> writes:
>>
>>>Is there a neat way to clean out a database via SQL commands?
>>>i.e. get rid of tables, sequences, integers, etc.
>>
>>>At present I'm using dropdb/createdb, but thats' far from ideal
>>>and I think it's causing postgres to do more mork than it needs to...
>>
>>Well, if you put everything into one or a few schemas then dropping
>>and recreating those schemas would do it.  I suspect though that this
>>is *not* faster than dropdb/createdb.
> 
> 
> Thanks Tom.
> 
> It's not just the speed, it's the constant deletes and creations in
> ~pgsql/data - as I said the other day, this is recreating a test db from the
> production one as part of unit tests, so this happens dozens of times a day...
> 
> 


-- 
-
Naomi Walker  Chief Information Officer
Eldorado Computing, Inc.  nwalker@eldocomp.com 602-604-3100
-
Courage is the power to let go the familiar. -Raymond Lindquist
-

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom 
it is addressed, and may contain information that is privileged, confidential 
and exempt from disclosure under applicable law. If you are not the intended 
addressee, nor authorized to receive for the intended addressee, you are hereby 
notified that you may not use, copy, disclose or distribute to anyone the 
message or any information contained in the message. If you have received this 
message in error, please immediately advise the sender by reply email, and 
delete the message. Thank you.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] empty a database

[Brad Nicholson]

If there are any foreign key constraints, you'll have to truncate the 
tables in the appropriate order.  You would also have to reset the 
sequence values as well.

Naomi Walker wrote:
How about just truncating all the tables?
Dick Davies wrote:
 

* Tom Lane <[EMAIL PROTECTED]> [0232 16:32]:
   

Dick Davies <[EMAIL PROTECTED]> writes:
 

Is there a neat way to clean out a database via SQL commands?
i.e. get rid of tables, sequences, integers, etc.
   

At present I'm using dropdb/createdb, but thats' far from ideal
and I think it's causing postgres to do more mork than it needs to...
   

Well, if you put everything into one or a few schemas then dropping
and recreating those schemas would do it.  I suspect though that this
is *not* faster than dropdb/createdb.
 

Thanks Tom.
It's not just the speed, it's the constant deletes and creations in
~pgsql/data - as I said the other day, this is recreating a test db from the
production one as part of unit tests, so this happens dozens of times a day...
   


 


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[ADMIN] who is best for support?

[Joel Fradkin]

Hi,

 

I am trying to get postgres working on a Dell and am finidng
my SQL queries run incrediably slow.

I have tried to adjust cache and buffers, etc with no luck.

Any advice on who would be best for help either it’s a
config issue or a query issue etc.

 

I been trying for a few weeks to get up to speed and
diagnose, but I am running out of time.

 

Joel Fradkin



 



Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305



 



[EMAIL PROTECTED]
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
© 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
 This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information.  Any unauthorized review,
use, disclosure or distribution is prohibited.  If you are not the
intended recipient, please contact the sender by reply email and delete and
destroy all copies of the original message, including attachments.



 




 

 

Re: [ADMIN] empty a database

[Christopher Browne]

Clinging to sanity, [EMAIL PROTECTED] (Dick Davies) mumbled into her beard:
> Is there a neat way to clean out a database via SQL commands?
>
> i.e. get rid of tables, sequences, integers, etc.
>
> At present I'm using dropdb/createdb, but thats' far from ideal
> and I think it's causing postgres to do more mork than it needs to...

If you truly need for all of the objects to go away, dropping the
database seems like a reasonable way to do this.  I'm not sure what
work you are imagining is "too much" or "unnecessary."

If you're regularly recreating a not-totally-empty database,
containing some set of "fresh" tables/sequences/views/such, then I'd
think you're doing the right thing, but need to take a further step...

If you're recreating a database that has some non-zero "initial
configuration," then what you might do is to set up a 'template'
database, let's call it "mytemplate" that contains that configuration.
Then you can do the following:

$ createdb --template=mytemplate mydatabase
CREATE DATABASE
$ do_some_work_with mydatabase
$ dropdb mydatabase
DROP DATABASE
$ createdb --template=mytemplate mydatabase
CREATE DATABASE
$ do_some_work_with mydatabase
$ dropdb mydatabase
DROP DATABASE
$ createdb --template=mytemplate mydatabase
CREATE DATABASE
$ do_some_work_with mydatabase
$ dropdb mydatabase
DROP DATABASE
$ createdb --template=mytemplate mydatabase
CREATE DATABASE
$ do_some_work_with mydatabase
$ dropdb mydatabase
DROP DATABASE
$ createdb --template=mytemplate mydatabase
CREATE DATABASE
$ do_some_work_with mydatabase
$ dropdb mydatabase
DROP DATABASE
-- 
If this was helpful,  rate me
http://linuxdatabases.info/~cbbrowne/slony.html
"Wintel:  A Wasteland  of  Useless  Software -  If  the bazillions  of
programs out there actually amount to something, why is everyone using
MICROS~1 Office, Word, Excel, PowerPoint, Access, ..."
-- [EMAIL PROTECTED]

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [ADMIN] who is best for support?

[Scott Marlowe]

On Tue, 2005-02-15 at 14:12, Joel Fradkin wrote:
> Hi,
> 
>  
> 
> I am trying to get postgres working on a Dell and am finidng my SQL
> queries run incrediably slow.
> 
> I have tried to adjust cache and buffers, etc with no luck.
> 
> Any advice on who would be best for help either itâs a config issue or
> a query issue etc.
> 
>  
> 
> I been trying for a few weeks to get up to speed and diagnose, but I
> am running out of time.

Is this a single thread running slow, or poor performance under heavy
parallel access?

What drive controller do you have?  The stock adaptec RAID controllers
are pretty pokey (the 3I ones)  But the megaraid add on (4C) seem pretty
fast.

Is the load transactional or analytical?

Generally throwing more drives at the problem in a large RAID array.

Having lots of ram (and letting the kernel manage most of it) is a good
idea.

Have you posted your postgresql.conf to the list?

Lastly, you might want to take this to the performance list, as that's
where most of the people who know about performance tuning hang out.

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] who is best for support?

[Marc G. Fournier]

On Tue, 15 Feb 2005, Joel Fradkin wrote:
Hi,

I am trying to get postgres working on a Dell and am finidng my SQL queries
run incrediably slow.
I have tried to adjust cache and buffers, etc with no luck.
Any advice on who would be best for help either it's a config issue or a
query issue etc.
Us? :)  Can you provide some more details?  Hardware configuration, type 
of query, operating system?


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] who is best for support?

[Richard_D_Levine]

I think Joel may be asking for a paid, dedicated consultant.  Could be
wrong, but I know you folks are out there.  --Rick



   
  "Marc G. Fournier"
   
  <[EMAIL PROTECTED]To:   Joel Fradkin <[EMAIL 
PROTECTED]>   
  rg>  cc:   
pgsql-admin@postgresql.org
  Sent by: Subject:  Re: [ADMIN] who is 
best for support?  
  [EMAIL PROTECTED] 
   
  tgresql.org   
   

   

   
  02/15/2005 03:36 PM   
   

   

   




On Tue, 15 Feb 2005, Joel Fradkin wrote:

> Hi,
>
>
>
> I am trying to get postgres working on a Dell and am finidng my SQL
queries
> run incrediably slow.
>
> I have tried to adjust cache and buffers, etc with no luck.
>
> Any advice on who would be best for help either it's a config issue or a
> query issue etc.

Us? :)  Can you provide some more details?  Hardware configuration, type
of query, operating system?


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [ADMIN] who is best for support?

[Joel Fradkin]

Us? :)  Can you provide some more details?  

Hardware configuration, 
Dell 6560 4 processor, 8 gig memory, I think I have Postgres on two raids
separate from the OS (pgsql is a raid 10 4 10kdrive array) and /wal is 2 15k
drives). I am running postgres 8 and am using the conf in
/var/lib/pgslq/data to point the data at /pgsql and a link to point the /wal
I set up inside the /pgsql/data folder to /wal.

type of query, 

I have cut out most of the query but even the first join makes it go to 27
seconds. In MSSQL with the first two left outers it takes 3 seconds.

explain analyze 
SELECT /*(tblaudit.name::text || ' '::text) || tblaudit.version::text AS
audit, tblaudit.active, "substring"((tblsections.section::text || ' '::text)
|| tblsections.sectionname::text, 1, 105) AS section, tblauditstatus.value
AS status, COALESCE(tblaudittypes.value, 'monthly'::character varying) AS
audittype, tblresponseheader.clientnum, tblresponseheader.auditid,
tblresponseheader.responseid, tblresponseheader.division,
tblresponseheader.auditnum, tblresponseheader.customauditnum,
tblresponseheader.dateaudittaken, tblresponseheader.createdby,
to_char(tblresponseheader.dateaudittaken, ''::text) AS "year",
to_char(tblresponseheader.dateaudittaken, 'q'::text) AS quarter,
to_char(tblresponseheader.dateaudittaken, 'MM'::text) AS "month",
to_char(tblresponseheader.dateaudittaken, 'D'::text) AS weekday,
to_char(tblresponseheader.dateaudittaken, 'WW'::text) AS week,
to_char(tblresponseheader.dateaudittaken, 'HH24:MI'::text) AS "time",
tbluser.completename AS keyedby, tblresponseheader.datecompleted,
tblresponseheader.dateauditkeyed, tblresponseheader.datekeyingcomplete,
tblresponseheader.pointsavailable AS auditpointsavailable,
tblresponseheader.pointsscored AS auditpointsscored, 
CASE tblresponseheader.pointsavailable
WHEN 0 THEN 'na'::text
ELSE COALESCE(to_char(tblresponseheader.pointsscored /
tblresponseheader.pointsavailable * 100::numeric, ''::text),
'na'::text)
END AS myaudittotalscore,*/ tblresponsesection.sectionid /*,
tblresponsesection.pointsavailable AS sectionpointsavailable,
tblresponsesection.pointsscored AS sectionpointsscored,
tblresponsesection.comments AS sectioncomments, tblresponse_line.questionid,
COALESCE(tblresponse_line.pointsscored, 0::numeric) AS pointsscored,
COALESCE(tblresponse_line.pointsavailable, 0::numeric) AS
questionpointsavailable, tbllocation.locationnum, tbllocation.name AS store,
tblregion.regionnum, tblregion.regionname AS region,
tbldistrict.districtnum, tbldistrict.districtname AS district,
tblresponse_line.y_n, tblresponse_line.answerid,
COALESCE("substring"(tblresponse_line.text_response, 1, 4048), 'na'::text)
AS text_response, tblanswers.answer, tblanswers.answertext,
tblanswers.answerdisplay, "substring"(tblquestions.question::text, 1, 105)
AS question, "substring"(tblquestions.questiondisplay::text, 1, 105) AS
questiondisplay, "substring"(tblquestions.qdescr::text, 1, 105) AS qdescr,
"substring"(tblquestions.qtext::text, 1, 105) AS qtext, tblquestions.qtext
AS qtextlong
*/
   FROM tblresponsesection
 /*  JOIN tblresponse_line ON tblresponsesection.sectionid =
tblresponse_line.sectionid AND tblresponsesection.responseid =
tblresponse_line.responseid AND tblresponsesection.clientnum =
tblresponse_line.clientnum
   JOIN tblresponseheader ON tblresponsesection.responseid =
tblresponseheader.responseid AND tblresponsesection.clientnum =
tblresponseheader.clientnum
   JOIN tbllocation ON tblresponseheader.locationid = tbllocation.locationid
AND tbllocation.clientnum = tblresponseheader.clientnum
   JOIN tbluser ON tblresponseheader.userid = tbluser.userid AND
tbluser.clientnum::bpchar = tblresponseheader.clientnum
   JOIN tbldistrict ON tbllocation.districtid = tbldistrict.districtid AND
tbldistrict.clientnum::bpchar = tblresponseheader.clientnum
   JOIN tblregion ON tbllocation.regionid = tblregion.regionid AND
tblregion.clientnum::bpchar = tblresponseheader.clientnum
   LEFT JOIN tblquestions ON tblresponse_line.clientnum =
tblquestions.clientnum AND tblresponse_line.sectionid =
tblquestions.sectionid AND tblresponse_line.questionid =
tblquestions.questionid AND tblresponseheader.auditid = tblquestions.auditid
   LEFT JOIN tblanswers ON tblresponseheader.auditid = tblanswers.auditid
AND tblresponse_line.clientnum = tblanswers.clientnum AND
tblresponse_line.sectionid = tblanswers.sectionid AND
tblresponse_line.questionid = tblanswers.questionid AND
tblresponse_line.answerid = tblanswers.answerid
   JOIN tblaudit ON tblresponseheader.auditid = tblaudit.auditid AND
tblresponse_line.clientnum = tblaudit.clientnum AND
tblaudit.defaultauditscoretype = 'p'::bpchar
   LEFT JOIN tblsections ON tblresponseheader.auditid = tblsections.auditid
AND tblresponse_line.clientnum = tblsections.clientnum AND
tblresponsesection.sectionid = tblsections.sectionid
   LEFT JOIN tblaudittypes ON tblresponseheader.audittypeid =
tblaudittypes.id AND 1 = tblaudittypes.presen

Re: [ADMIN] who is best for support?

[Scott Marlowe]

On Tue, 2005-02-15 at 15:21, Joel Fradkin wrote:
> Us? :)  Can you provide some more details?  
> 
> Hardware configuration, 
> Dell 6560 4 processor, 8 gig memory, I think I have Postgres on two raids

four processors, or two hyperthreaded cpus?  My experience has been that
hyperthreading usually doesn't help, and often hurts performance.  This
may have improved with later model 2.6 kernels.

> separate from the OS (pgsql is a raid 10 4 10kdrive array) and /wal is 2 15k
> drives). I am running postgres 8 and am using the conf in
> /var/lib/pgslq/data to point the data at /pgsql and a link to point the /wal
> I set up inside the /pgsql/data folder to /wal.

Which RAID Controller are you using?  Does it have battery backed cache?

> I have cut out most of the query but even the first join makes it go to 27
> seconds. In MSSQL with the first two left outers it takes 3 seconds.

Are the columns you are joining on the same type?  Are they integers
(the fastest) or numerics (slow) or text (also slow)?

Is there a reason you're joining on so many different fields for each
table?  If there is a good model of FK relations, that isn't usually
necessary.

IT looks kinda like there's some redundancy in the join on clauses by
how may of them are from all over the place in there.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] Problems with dump

[Michael Fuhr]

On Tue, Feb 15, 2005 at 08:50:53AM -0300, Ricardo Valença de Assis wrote:
> 
> I'm trying to migrate a database from one server to another but I got
> a hard problem. All the tables and sequences are migrated but the field
> last_value of ALL sequences is set to 1. Why this? The others fields of
> them are set correctly but last_value it not. Is there a way to dump
> only sequences?

What versions of PostgreSQL are the servers running?  What commands
did you run to dump and restore the database?  Does a restore print
errors?  If so, what are they?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 8: explain analyze is your friend