[SQL] pgdump by schema?
Is there a way to dump everything in a particular schema? Bradley Miller NUVIO CORPORATION Phone: 816-444-4422 ext. 6757 Fax: 913-498-1810 http://www.nuvio.com [EMAIL PROTECTED]
Re: [SQL] pgdump by schema?
am Wed, dem 02.02.2005, um 9:33:22 -0600 mailte Bradley Miller folgendes: > Is there a way to dump everything in a particular schema? RTFM. man pg_dump, search for --schema Regards, Andreas -- Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau- fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pgdump by schema?
From: pg_dump --help -n, --schema=SCHEMA dump the named schema only > Is there a way to dump everything in a particular schema? > > > Bradley Miller > NUVIO CORPORATION > Phone: 816-444-4422 ext. 6757 > Fax: 913-498-1810 > http://www.nuvio.com > [EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] pgdump by schema?
On Wed, Feb 02, 2005 at 09:33:22AM -0600, Bradley Miller wrote: > Is there a way to dump everything in a particular schema? See the documentation for pg_dump. In PostgreSQL 7.4 and later, pg_dump has a --schema (-n) option. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] locale with os x
The solution to locales on OS X. initdb --locale=es_ES.ISO8859-1 ~/testdb worked great the settings are at: /usr/share/locale/ I asked on the OS X discussion board in the UNIX section... Ted __ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pgdump by schema?
Thanks -- I was looking at a book and it didn't mention the schema dump . . . should have done the man or --help before asking. On Feb 2, 2005, at 9:46 AM, Adam Witney wrote: From: pg_dump --help -n, --schema=SCHEMA dump the named schema only Bradley Miller NUVIO CORPORATION Phone: 816-444-4422 ext. 6757 Fax: 913-498-1810 http://www.nuvio.com [EMAIL PROTECTED]
[SQL] pg_restore problem
I'm attempting to restore a dump from one server to another (one is a Mac and one is a Linux base, if that makes any difference). I keep running into issues like this: pg_restore: [archiver (db)] could not execute query: ERROR: function public.random_page_link_id_gen() does not exist This is what I'm using to restore the files with: pg_restore -O -x -s -N -d nuvio mac_postgres_2_2_2005_13_24 Any suggestions on how to get around this problem? It's a huge pain so far just to sync my two servers up. Bradley Miller NUVIO CORPORATION Phone: 816-444-4422 ext. 6757 Fax: 913-498-1810 http://www.nuvio.com [EMAIL PROTECTED]
Re: [SQL] pg_restore problem
I used pgadmin to save and mine would not restore saying something about the encoding. I will have to be able to save and restore reliably as well. Also I never heard anything further on the query running slow (I put up table defs and analyze with and without seq on). I am running into this on several of my views (I guess I am not too bright, because I still don’t get why it chooses seq scan on indexed tables). I can force it to use index and did see a little improvement, but the MSSQL was 3 secs and Postgres was like 9. Seeing as how I got the one viw to return faster (it was very complex view) on postgres, my guess is I still have stuff to do. I did try changing the cost to a lower number in config and redid my analyze, but it was still trying to do a seq scan. 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. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bradley Miller Sent: Wednesday, February 02, 2005 3:17 PM To: Postgres List Subject: [SQL] pg_restore problem I'm attempting to restore a dump from one server to another (one is a Mac and one is a Linux base, if that makes any difference). I keep running into issues like this: pg_restore: [archiver (db)] could not execute query: ERROR: function public.random_page_link_id_gen() does not exist This is what I'm using to restore the files with: pg_restore -O -x -s -N -d nuvio mac_postgres_2_2_2005_13_24 Any suggestions on how to get around this problem? It's a huge pain so far just to sync my two servers up. Bradley Miller NUVIO CORPORATION Phone: 816-444-4422 ext. 6757 Fax: 913-498-1810 http://www.nuvio.com [EMAIL PROTECTED]
[SQL] PL/PgSQL - returning multiple columns ...
I have a function that I want to return 'server_name, avg(load_avg)' ... if I wanted to return matching rows in a table, I can do a 'setof ', with a for loop inside ... but what do I set the 'RETURNS' to if I want to return the results of query that returns only two fields of a table, or, in the case of the above, one column and oen 'group by' column? thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] pg_restore problem
Bradley Miller <[EMAIL PROTECTED]> writes: > I'm attempting to restore a dump from one server to another (one is a > Mac and one is a Linux base, if that makes any difference). I keep > running into issues like this: > pg_restore: [archiver (db)] could not execute query: ERROR: function > public.random_page_link_id_gen() does not exist Is this a problem of items in the dump being in the wrong order (ie, there's a forward reference to random_page_link_id_gen())? > Any suggestions on how to get around this problem? Use 8.0 ... or use pg_restore's -L/-l options to manually adjust the load order. Pre-8.0 versions of pg_dump are easily fooled if you use ALTER to make earlier-created objects reference later-created objects. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PL/PgSQL - returning multiple columns ...
Hi Marc,
One option is to create a simple data type and return the rowtype of the
datatype
eg CREATE TYPE tserverload AS ("server_name" text, "load_avg" int4);
CREATE FUNCTION getserverload()
RETURNS tserverload
AS
'DECLARE
r tserverload%rowtype;
etc.
You would then return r, comprised of r.server_name and r.load_avg.
George
- Original Message -
From: "Marc G. Fournier" <[EMAIL PROTECTED]>
To:
Sent: Wednesday, February 02, 2005 3:10 PM
Subject: [SQL] PL/PgSQL - returning multiple columns ...
I have a function that I want to return 'server_name, avg(load_avg)' ...
if I wanted to return matching rows in a table, I can do a 'setof
', with a for loop inside ... but what do I set the 'RETURNS' to if
I want to return the results of query that returns only two fields of a
table, or, in the case of the above, one column and oen 'group by' column?
thanks ...
Marc G. Fournier Hub.Org Networking Services
(http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ:
7615664
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] problem with postgres
Dear friends I hope some one can help me Server machine celeron 2.4Gb with 512 Ram postgres 7.4.5 conectiva 8 with kernel 2.4.19 i´m getting many message of Erro:canceling query due to user request how to solve this i even get with an just booted up server, and running vacuum analyze verbose inside psql with no other people connected the only changes i has on postgresql.conf is max_connection=512 shared_buffers=8192 sort_mem=8192 vacuum_mem=31792 Regards Luiz Rafael ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] plpgsql functions to 'C' functions
I would like to convert all my plpgsql functions to their 'C' equivalent coded functions. Does anyone have experience in this matter? Thank you, Peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] problem with postgres
Dear friends I hope some one can help me Server machine celeron 2.4Gb with 512 Ram postgres 7.4.5 conectiva 8 with kernel 2.4.19 i´m getting many message of Erro:canceling query due to user request how to solve this i even get with an just booted up server, and running vacuum analyze verbose inside psql with no other people connected the only changes i has on postgresql.conf is max_connection=512 shared_buffers=8192 sort_mem=8192 vacuum_mem=31792 Regards Luiz Rafael ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] problem while converting sybase quries to postgres
The following is a sybase query and i am trying to convet it into postgres but in postgres the join will be specified in from clause and we cannot specify any logical operator i would be grateful if you could tell me the solution for it SELECT t1.SR_TRAN_HEAD_PK AS ReceiptHeaderPK, t1.ADJ_TRAN_ID AS AdjustmentTransactionID, t1.TOTAL_QTY_INVOICED AS TotalQtyInvoiced, t1.TOTAL_QTY_MATCHED AS TotalQtyMatched, t1.VENDOR_ID AS VendorID, t1.COMMENTS AS Comments, t1.SR_NUMBER AS ShipmentReceiptNumber, t1.SHIPMENT_RECEIPT_DATE AS ReceiptDate, t1.POSTING_DATE AS PostingDate, t2.PO_HEADER_PK AS POHeaderPK, t1.VENDOR_DOCUMENT_NUMBER AS VendorDocumentNumber, t1.TRAN_STATUS_ID AS TransactionStatusID, t1.SR_TOTAL AS ReceiptTotal, t1.SR_BATCH_NUMBER AS ReceiptBatchNumber, t1.VENDOR_PK AS VendorPK, t1.TOTAL_QTY_RECEIVED AS TotalQtyReceived, t1.TOTAL_QTY_REJECTED AS TotalQtyRejected, t2.PO_NUMBER AS PONumber, t2.BUYER_PK AS BuyerPK, t2.BILLING_TERMS_PK AS BillingTermsPK, t2.PO_GROUP_PK AS POGroupPK, t2.PURCHASE_ORDER_TYPE_PK AS POTypePK, t2.PO_DELIVERY_METHOD_PK AS PODeliveryMethodPK, t2.CUSTOMER_PK AS CustomerPK, t2.PO_HEADER_STATUS_PK AS POHeaderStatusPK, t2.BUYER_ID AS BuyerID, t2.PO_GROUP AS POGroup, t2.PO_TYPE AS POType, t2.PO_DATE AS PODate, t2.PO_HEADER_STATUS AS POStatus, t2.IS_PO_ON_HOLD AS POonHold, t2.TRADE_DISCOUNT AS TradeDiscount, t2.SHIPPING_CHARGES AS ShippingCharges, t2.OTHER_CHARGES AS OtherCharges, t2.PO_TAX AS POTax, t2.PO_SUB_TOTAL AS POSubTotal, t2.PO_DISCOUNT AS PODiscount, t2.PO_TOTAL AS POTotal, t2.PO_TOTAL_QTY AS TotalQty, t2.RELEASED_DATE AS ReleasedDate, t3.AMT_APPORTIONED AS AmtApportioned, t3.MODIFIED_RECEIVED_QTY AS ModifiedReceivedQty, t3.QTY_INVOICED AS QtyInvoiced, t3.QTY_MATCHED AS QtyMatched, t3.STATUS_ID AS StatusID, t3.INV_COST AS InvoiceCost, t3.AMT_INVOICE_APPORTIONED AS AmtInvoiceApportioned, t3.SR_TRAN_DETAIL_PK AS ReceiptDetailPK, t3.WAREHOUSE_PK AS WarehousePK, t3.UNIT_COST AS UnitCost, t3.QTY_RECEIVED AS QtyReceived, t3.EXT_COST AS ExtendedCost, t3.QTY_REJECTED AS QtyRejected, t3.REASON_FOR_REJECTION AS ReasonforRejection, t3.BILL_OF_LADING AS BillofLading, t3.ITEM_ID AS ItemID, t3.WAREHOUSE_ID AS WarehouseID, t3.ITEM_PK AS ItemPK, t3.UOM_PK AS UomPK, t3.UOM_ID AS UomID, t3.QTY_FOR_BASE_UOM AS BaseUOM, t3.UNIT_COST_FOR_BASE_UOM AS UnitCostforBaseUOM, t3.ITEM_NAME AS ItemName, t3.ITEM_ALTERNATE_NAME AS ItemAlternateName, t3.PO_DETAIL_PK AS PODetailPK FROM PE_POP_SR_POSTED_TRAN_HEAD t1, PE_POP_PO_HEADER t2, PE_POP_SR_POSTED_TRAN_DET t3 Where t1.SR_TRAN_HEAD_PK = t3.SR_TRAN_HEAD_PK and (t1.PO_HEADER_PK *= t2.PO_HEADER_PK or t3.PO_HEADER_PK *= t2.PO_HEADER_PK)
Re: [SQL] plpgsql functions to 'C' functions
On Wed, Feb 02, 2005 at 05:57:10AM -0800, Peter Manchev wrote: > I would like to convert all my plpgsql functions to their 'C' equivalent > coded functions. Why? What problem are you trying to solve? > Does anyone have experience in this matter? See "C-Language Functions" in the documentation: http://www.postgresql.org/docs/8.0/static/xfunc-c.html If you're having a specific difficulty then please show the code you're using, explain what you want it to do, and describe what acutally happens. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] problem with postgres
On Wed, Feb 02, 2005 at 11:33:03PM -0200, Luiz Rafael Culik Guimaraes wrote: > i´m getting many message of > Erro:canceling query due to user request What does the following query show? SHOW statement_timeout; If statement_timeout isn't 0 (zero) and if you didn't change it in postgresql.conf, then maybe you're picking it up from a per-user or per-database configuration. You can check such settings with the following queries: SELECT usename, useconfig FROM pg_user; SELECT datname, datconfig FROM pg_database; You can use ALTER USER or ALTER DATABASE to change the settings. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
