Re: [ADMIN] Random server overload
-Original Message- From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin- ow...@postgresql.org] On Behalf Of Viktor Sent: Tuesday, October 01, 2013 9:19 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Random server overload Hello, We are experiencing database random overloads caused by IDLE processes. Their count jumps from normal ~70 connections to 250-300 with high I/O (30- 40% wa, when normal ~ 1 % wa). The overload isn't long and lasts about 5 -10 minutes just a couple of times during the month. Please suggest how to debug this issue and find the cause of the overloads. Or mby we should tune our config file ? errorlog example: 2013-09-30 10:37:45 EEST FATAL: sorry, too many clients already 2013-09-30 10:37:45 EEST FATAL: remaining connection slots are reserved for non-replication superuser connections ... config file: max_connections = 250 shared_buffers = 16GB temp_buffers = 16MB max_prepared_transactions = 0 work_mem = 448MB maintenance_work_mem = 4GB max_stack_depth = 6MB wal_buffers = 18MB checkpoint_segments = 30 checkpoint_timeout = 5min checkpoint_warning = 30s random_page_cost = 4.0 cpu_tuple_cost = 0.01 cpu_index_tuple_cost = 0.005 effective_cache_size = 50GB default_statistics_target = 100 autovacuum = on othr values are defaults. System: RAM 74 GB PostgreSQL 9.1.9, Debian 6 Database size on disc: 84 GB data + 23 GB indexes. Different LVMs on RAID 10. -- Best regards Did you try using any kind of connection pooler, e.g. PgBouncer? Should help. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Catch exceptions outside function
-Original Message- From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin- ow...@postgresql.org] On Behalf Of Roberto Grandi Sent: Wednesday, September 18, 2013 6:17 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Catch exceptions outside function Dear all I ask for your help cause I can't point out the solution to my problem on PG 8.3 I would catch an exception outside any function/procedure but directly within script. BEGIN; -- raise an exception code EXCEPTION WHEN 'exception_type' THEN ROLLBACK; COMMIT; is it possible with PG 8.3? Many thanks in advance. Roberto No. It's not possible in 8.3. What you want is basically anonymous plpgsql block, EXCEPTION - is plpgsql, not pure sql, could be used only inside plpgsql function in 8.3. OTOH, starting with 9.0 you can use anonymous plpgsql blocks, and get what you asked for. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] How do I know my table is bloated?
From: Rodrigo Barboza [mailto:rodrigombu...@gmail.com] Sent: Thursday, May 30, 2013 2:50 PM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] How do I know my table is bloated? Well, maybe I am. But I am worried because I know that there are some tables that do lots of updates and delete. As this concept is new for me, I am trying to be prepared to detect a situation like this. -- It all depends on pattern of your inserts/updates/deletes. If your index accumulates lots of almost (but not completely) empty pages with just few entries left, than - yes, REINDEX is your friend. b.t.w., this concept is not unique to Postgres, it's just a nature of B-tree indexes. Igor -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] exceeded MAX_ALLOCATED_DESCS while trying to open file
Hi Steve, Each SELECT opens your FOREIGN TABLE so you are opening it 10 times. This is hardcoded by a #define in ./src/backend/storage/file/fd.c during the build so you would need to recompile the software yourself to change it. Can you re- write your query using a single SELECT or possibly read the data into a temporary table for processing? Regards, Ken Hi Ken, So, it means that number of (different) foreign tables joined in single select cannot exceed 10, right? Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Migration from Oracle 11g to Postgresql
From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Silvana Bravo Sent: Thursday, May 02, 2013 9:58 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Migration from Oracle 11g to Postgresql Hello, I'm planning a migration of production DBs from Oracle to Postgresql. I would like to receive some suggestions, advising or links to related subjects. I decided to rewrite all store procedures, that is not a problem. But regarding the data I was thinking in migrating schema by schema. First, create all table structures in postgresql schema. Then, generate inserts statements from Oracle schema to extract all data. Addapt those inserts to the .sql script/s in postgresql schema. I'm currently making a POC of that process since the schemas to migrate are not so big. Only a few tables have around 1.000.000 of rows. That would be the manual migration. Are there some other ways to make this? Thanks for the support and I'm glad to start with postgres. Oracle has so called heterogeneous services that would allow to connect directly (creating db link) to Postgres ODBC data source, that you could point to your Postgres database. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] DBLink
From: pgsql-admin-ow...@postgresql.org
[mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Thomaz Luiz Santos
Sent: Thursday, April 25, 2013 10:22 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] DBLink
hello! pgAdmins :D
I am trying to use the dblink in a trigger, however when the computer (source)
that is running the triggers are not accessible by ethernet to the target
computer, the dblink returns me an error and the trigger is not executed as
planned, it is terminated and input records in the table are not written.
my idea is that the insert is done in the table on the local computer if the
local computer has access to ethernet it should send the data to another
computer using the dblink, but do not have access ethernet writes in the local
table.
thank you.
INSERT INTO teste(id, valor) VALUES (1,'valor'); -- On Ethernet, connected OK
INSERT INTO teste(id, valor) VALUES (2,'valor'); -- Off Ethernet, disconnected
ERROR.
Error:
ERRO: could not establish connection
DETAIL: não pôde conectar ao servidor: No route to host (0x2751/10065)
O servidor está executando na máquina 192.168.102.23 e aceitando
conexões TCP/IP na porta 5432?
CONTEXT: comando SQL SELECT (select count(*) from
dblink_exec('host=192.168.102.23 port=5432 dbname=teste password=admin ', '' ||
$1 || '', false))
PL/pgSQL function senddatato line 6 at atribuição
comando SQL SELECT (SELECT SendDataTo(SQL))
PL/pgSQL function teste_after_insert line 8 at PERFORM
** Error **
ERRO: could not establish connection
SQL state: 08001
Context: comando SQL SELECT (select count(*) from
dblink_exec('host=192.168.102.23 port=5432 dbname=teste password=admin ', '' ||
$1 || '', false))
PL/pgSQL function senddatato line 6 at atribuição
comando SQL SELECT (SELECT SendDataTo(SQL))
PL/pgSQL function teste_after_insert line 8 at PERFORM
Code:
CREATE DATABASE teste
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'Portuguese, Brazil'
LC_CTYPE = 'Portuguese, Brazil'
CONNECTION LIMIT = -1;
CREATE TABLE teste
(
id bigint NOT NULL,
valor text,
CONSTRAINT teste_pkey PRIMARY KEY (id )
)
WITH (
OIDS=FALSE
);
ALTER TABLE teste
OWNER TO postgres;
CREATE OR REPLACE FUNCTION senddatato(sql text)
RETURNS integer AS
$BODY$
DECLARE
ServerON int;
BEGIN
ServerON := (select count(*) from dblink_exec('host=192.168.102.23
port=5432 dbname=teste password=admin ', '' || $1 || '', false));
return ServerON;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION senddatatoserver(text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION teste_after_insert()
RETURNS trigger AS
$BODY$
DECLARE
SQL text;
BEGIN
SQL := 'insert into teste(id,valor) values (' || NEW.id || ',' || ||
NEW.valor || || ')';
IF (true) THEN
PERFORM(SELECT SendDataTo(SQL));
RETURN NEW;
END IF;
RETURN NEW;
RAISE NOTICE 'gravado local!';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION teste_after_insert()
OWNER TO postgres;
--
--
Thomaz Luiz Santos
Linux User: #359356
http://thomaz.santos.googlepages.com/
Did you try to intercept this error with exception handler inside
senddatato(...) function?
See PG docs:
http://www.postgresql.org/docs/9.2/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
Regards,
Igor Neyman
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] unlooged tables
Hi, Where in pg_catalog I can find, if the table was created as unlogged (or not)? As in: create unlogged table t1(c1 int); I can't seem to find this info in pg_catalog tables/views. Tried psql with -E to describe (\d) unlogged table, but it was not helpful. Regards, Igor Neyman
Re: [ADMIN] unlooged tables
From: Igor Neyman Sent: Tuesday, November 06, 2012 4:09 PM To: pgsql-admin@postgresql.org Subject: unlooged tables Hi, Where in pg_catalog I can find, if the table was created as unlogged (or not)? As in: create unlogged table t1(c1 int); I can't seem to find this info in pg_catalog tables/views. Tried psql with -E to describe (\d) unlogged table, but it was not helpful. Regards, Igor Neyman Sorry for the noise. As soon as previous message was sent I found it: relpersistence column in pg_class (u for unlogged tables, p - for normal tables) Igor N. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Recreate primary key without dropping foreign keys?
-Original Message- From: Chris Ernst [mailto:cer...@zvelo.com] Sent: Monday, April 16, 2012 10:55 PM To: pgsql-admin@postgresql.org Subject: Re: Recreate primary key without dropping foreign keys? On 04/16/2012 07:02 PM, amador alvarez wrote: How about deferring the FK's while recreating the PK ? or using a temporary parallel table to be pointed by the other tables (FK) and swap it up on the recreation. Hmm.. Interesting. But it appears that you have to declare the foreign key as deferrable at creation. Is there any way to set an existing foreign key as deferrable? - Chris May be this (from the docs) would help: ADD table_constraint [ NOT VALID ] This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option. Using this option you can drop and recreate corresponding FKs in a very short time, and start using them, while postponing to run VALIDATE CONSTRAINT for later. It's similar to Oracle's adding FK with NOCHECK option, but if IRC there is no need to run VALIDATE CONSTRAINT later. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] logfile per DataBase
-Original Message- From: Lutz Steinborn [mailto:l.steinb...@4c-ag.de] Sent: Friday, February 24, 2012 8:55 AM To: pgsql-admin@postgresql.org Subject: logfile per DataBase Hello, is it possible to configure postgresql to produce one logfile per database in a cluster? I can't find any hint about this in the doc. Something like: log_filename = 'postgresql-[DBNAME]-%Y-%m-%d_%H%M%S.log' It would be very use full for a development server. have a happy weekend Lutz No, it is not possible. But, inside log file you can use %d for log_line_prefix to specify which db emitted particular line. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Database alias
-Original Message- From: Florian Weimer [mailto:fwei...@bfk.de] Sent: Wednesday, August 24, 2011 5:00 AM To: Gabriele Bartolini Cc: pgsql-admin@postgresql.org Subject: Re: Database alias * Gabriele Bartolini: On Wed, 24 Aug 2011 08:30:34 +, Florian Weimer fwei...@bfk.de wrote: We've got some systems which use a historic database name and would like to transition them to our current naming scheme. Is there support for some form of database aliases? If you are trying to make the database seen by clients under a different name, probably the most practical solution would be to add a PgBouncer layer in front of the clients which maps the old database name to the new one. http://wiki.postgresql.org/wiki/PgBouncer Ah, that would probably work at a technical level, thanks. But in this particular case, my aim is to avoid additional complexity, and installing PgBouncer seems to be at odds with this goal. 8-) -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 PgBouncer is the easiest piece of software to install and to manage I've ever dealt with, and it's very light on system resources. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgres service starts and then stops immediatly
From: Sofer, Yuval [mailto:yuval_so...@bmc.com] Sent: Thursday, August 11, 2011 6:01 AM To: pgsql-admin@postgresql.org Cc: Zucker, Yehudit Subject: Postgres service starts and then stops immediatly Hi, -We are using Postgres 8.3.7 on Windows -We cannot start the Postgres service. The windows OS error message is: The PostgreSQL Server 8.3 service on Local Computer started then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service. -No logs in the postgres log file -When using pg_ctl, the postgres.exe is starting OK Please help, Thanks, Yuval Sofer BMC Software CTMD Business Unit DBA Team 972-52-4286-282 yuval_so...@bmc.com Yuval, Using pg_ctl, did you try to start PG as a service? e.g.: C:/PostgreSQL/8.4/bin/pg_ctl.exe runservice -N PostgreSQL -D C:/PostgreSQL/8.4/data -w Could it be that account used to start PG service doesn't have privileges to run as a service? Check in Local Security Settings under Local Policies/User Rights Assignments - make sure that Log on as a service granted to the account specified in PG service properties. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] revoked permissions on table still allows users to see table's structure
-Original Message- From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov] Sent: Friday, July 22, 2011 10:33 AM To: Juan Cuervo (Quality Telecom); Bob Lunney Cc: pgsql-admin@postgresql.org Subject: Re: revoked permissions on table still allows users to see table's structure I don't think I've used any database where the *structure* of database objects was hidden from someone connected to the database. There are typically system tables of some sort to which all authorized users in the database have read-only access. The parallel I would draw in the real world is that the format of the forms which are required for adoption in the Wisconsin court system are a matter of public record -- anybody can see the blank forms. Getting a look at data which has been entered onto such forms is a very different matter. If you want to hide the structure of the tables from a person, you need to deny that person authority to connect to the database. You can always allow such a person to connect to an application which you are running in a trusted environment. -Kevin Not exactly. In Oracle user needs to be granted SELECT_CATALOG_ROLE role in order to get SELECT privileges on data dictionary views. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] odbc
From: Marc Fromm [mailto:marc.fr...@wwu.edu] Sent: Tuesday, March 29, 2011 1:52 PM To: pgsql-admin@postgresql.org Subject: odbc I am running postgres on a red hat linux server. postgresql-python-8.1.23-1.el5_6.1 postgresql-test-8.1.23-1.el5_6.1 postgresql-libs-8.1.23-1.el5_6.1 postgresql-docs-8.1.23-1.el5_6.1 postgresql-contrib-8.1.23-1.el5_6.1 postgresql-8.1.23-1.el5_6.1 postgresql-pl-8.1.23-1.el5_6.1 postgresql-odbc-08.01.0200-3.1 postgresql-jdbc-8.1.407-1jpp.4 postgresql-server-8.1.23-1.el5_6.1 postgresql-tcl-8.1.23-1.el5_6.1 User on windows computers would like to odbc to the postgres databases. I downloaded the windows postgres odbc drivers from the following site and installed version psqlodbc-08_01_0200 on the windows computer. http://ftp9.us.postgresql.org/pub/mirrors/postgresql/odbc/versions/msi/ When I configure a File Data Source connection in access, I cannot connect to the postgresql database. I get the message, A connection could not be made using the file data source parameters entered. Save non-verified file DSN? The parameters entered are: Database: database_name Server: I entered the IP address of the server User Name: I entered postgres Password: SSL Mode: prefer Port: 5432 (which is the port used by postgres) Do I need to configure the /etc/odbc.ini file? It is currently just a blank file. Thanks for any help. Marc [I.N.] You need to create System Data Source (or User) - not File Data Source. Regards, Igor Neyman
Re: [ADMIN] Postgres Backup Utility
-Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Thursday, January 20, 2011 12:51 PM To: Bradley Holbrook Cc: French, Martin; pgsql-admin@postgresql.org Subject: Re: Postgres Backup Utility On Thu, Jan 20, 2011 at 10:42 AM, Bradley Holbrook operations_brad...@servillian.ca wrote: Thanks Scott... a couple comments. Our developers never decide what goes to where... they just happily plumb away on the development db until we're ready to take our product to testing (at regular intervals), once QA is passed, we wish to apply these to live. We have several diff tools and sync tools, but they take forever (especially the ones that only go one schema at a time). The DDL Logging sounds like a sufficient solution, can it be configured to only record create and alter commands (or create or replace commands on functions or updates on sequences, etc)? I'd likely write a script to have this emailed to me at the end of every day. I'm going to google DDL logging (never heard of it), but any good resources off the top of your head? It's basically logging anything that changes the structure of the database. It would be easy enough to grep out what you do and don't want later. Martin French is right though, ask your developers to write down all their SQL struct changes and they look at you funny... and being a developer myself I'd look at me funny. If you forget just once you're screwed into a day sifting through tables and code. I've worked in three different shops now as a dev-dba and sysadmin, and in all three, all DDL changes had to be committed and / or handed over to the DBAs. period. Look funny all they want, they either give up the DDL or their code doesn't get pushed off dev servers onto anything else. At the very least they should be able to tell you which tables changed to go with which code changes, or you're not sure what code you can and can't push. I get both of your point on this, but it's a discipline issue that needs sorting out with the developers if you want to have reproduceable ddl changes in all your systems that match the code changes. Completely agree with Scott. Only want to add that in this kind of development environment: development/test/production - Source code versioning software is absolute necessity (there are many: CSV, SourceSafe, Perforce, ... - pick your choice). Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] could not connect to server: Connection refused (0x0000274D/10061)
From: Anuj Pankaj [mailto:an...@cybage.com] Sent: Wednesday, September 29, 2010 8:57 AM To: pgsql-admin@postgresql.org Subject: could not connect to server: Connection refused (0x274D/10061) Hi, I installed Postgres 8.1.4 on Linux and postgres server is running and I successfully created database on local machine. I modified listen_addresses = '*' in Postgresql.conf and however If I try to access postgres through pgAdmin III. I am sticking with an exception. Exception is: could not connect to server: Connection refused (0x274D/10061) Is the server running on host 192.168.9.126 and accepting TCP/IP connections on port 5432? Thanks Regards, Anuj Pankaj Legal Disclaimer: This electronic message and all contents contain information from Cybage Software Private Limited which may be privileged, confidential, or otherwise protected from disclosure. The information is intended to be for the addressee(s) only. If you are not an addressee, any disclosure, copy, distribution, or use of the contents of this message is strictly prohibited. If you have received this electronic message in error please notify the sender by reply e-mail to and destroy the original message and all copies. Cybage has taken every reasonable precaution to minimize the risk of malicious content in the mail, but is not liable for any damage you may sustain as a result of any malicious content in this e-mail. You should carry out your own malicious content checks before opening the e-mail or attachment. www.cybage.com [I.N.] Do you have proper configuration (that allows network connections) in your pg_hba.conf file? Regards, Igor Neyman
Re: [ADMIN] fail-safe sql update triggers
-Original Message- From: Michael Monnerie [mailto:michael.monne...@is.it-management.at] Sent: Friday, September 03, 2010 9:03 AM To: pgsql-admin@postgresql.org Subject: fail-safe sql update triggers I want to log all activity from a table to a old_table. Creating an ON INSERT trigger is simple, it just needs to INSERT INTO old_filter SELECT NEW.*; in a procedure that is called via the trigger. But what about updates? There is no simple UPDATE old_filter SET NEW.* WHERE id=NEW.id; so I would need to declare each column like UPDATE old_filter SET field1=NEW.field1,f2=NEW.f2, where id=NEW.id; But that is error prone, because when the filter table is changed to have a new column, the UPDATE statement would not contain it. Is there a fail-proof shortcut? mit freundlichen Grüßen Michael Monnerie, Ing. BSc Michael, You are seeing only half of the problem. When you modify your filter table (i.e. add a column), not only UPDATE statement in your trigger function should be modified to reflect the change in the original filter table, but also your history table old_filter should be modified as well: new column should be added. So, in short there is no easy way around. When source table is modified, destination table and trigger function should be also modified. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] parallel option in pg_restore
I'm testing 8.4.4 (on Windows) before upgrading our app to this PG version. When running pg_restore with -j 2 parallel option, I'm getting the following error: pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required) in the log file. Mind you, the backup (which I'm restoring here) was done in custom mode ( -F c) using pg_dump version 8.2.5. Is this error results from version differences between pg_dump and pg_restore? The reason I'm using old backups (created with older pg_dump version) is that I'm trying to save time during upgrade, and I have these big backup files already created. TIA, Igor Neyman
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, June 22, 2010 10:37 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore Igor Neyman iney...@perceptron.com writes: I'm testing 8.4.4 (on Windows) before upgrading our app to this PG version. When running pg_restore with -j 2 parallel option, I'm getting the following error: pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required) We have gotten several reports of this, but none of the developers have been able to reproduce it. Can you provide an exact test case? regards, tom lane Tom, Backup files I'm trying to restore in parallel contain partitions of several partitioned tables. Tables partitioned by month, each backup file contains 1 month worth of data for all partitioned tables. Before restoring backed up partitions, I'm restoring from another backup file (not using -j), which contains base (empty) tables, from which partitions inherited. And this restore runs fine. Is that the information you asked for, or you want a sample of small backup file attached? I'm attaching pg_restore log file, if it's of any help. Regards, Igor Neyman CM_200608_Restore.log Description: CM_200608_Restore.log -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: John Rouillard [mailto:rou...@renesys.com] Sent: Tuesday, June 22, 2010 11:52 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore On Tue, Jun 22, 2010 at 11:05:02AM -0400, Igor Neyman wrote: Igor Neyman iney...@perceptron.com writes: I'm testing 8.4.4 (on Windows) before upgrading our app to this PG version. When running pg_restore with -j 2 parallel option, I'm getting the following error: pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required) We have gotten several reports of this, but none of the developers have been able to reproduce it. Can you provide an exact test case? regards, tom lane Backup files I'm trying to restore in parallel contain partitions of several partitioned tables. Tables partitioned by month, each backup file contains 1 month worth of data for all partitioned tables. Before restoring backed up partitions, I'm restoring from another backup file (not using -j), which contains base (empty) tables, from which partitions inherited. And this restore runs fine. I realise this may be a silly question (especially for windows), but the fseek complaint has me wondering. Are you running a pipleine reatore? E.G: type dumpfile | pg_restore -j 2 or are you running: pg_restore -j 2 dumpfile in the latter case it should be fseekable, but in the former case I don't think you can fseek stdin on either windows or *nix.. -- -- rouilj John Rouillard System Administrator Renesys Corporation 603-244-9084 (cell) 603-643-9300 x 111 No piping, just regular restore from the backup file. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: Glyn Astill [mailto:glynast...@yahoo.co.uk] Sent: Tuesday, June 22, 2010 12:36 PM To: John Rouillard; Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore --- On Tue, 22/6/10, Igor Neyman iney...@perceptron.com wrote: From: Igor Neyman iney...@perceptron.com Subject: Re: [ADMIN] parallel option in pg_restore To: John Rouillard rou...@renesys.com Cc: pgsql-admin@postgresql.org Date: Tuesday, 22 June, 2010, 17:34 No piping, just regular restore from the backup file. Same here. If only I could get a small sample which exhibited the issues - so far I can only get the same error with large dump files. I just sent some samples in reply to Tom's request. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov] Sent: Tuesday, June 22, 2010 12:40 PM To: Igor Neyman; John Rouillard; Glyn Astill Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore Glyn Astill glynast...@yahoo.co.uk wrote: so far I can only get the same error with large dump files. Large being a relative term -- ever see it on a file smaller than 2GB? -Kevin Yes, just sent couple to the list. Igor -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, June 22, 2010 1:10 PM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore Igor Neyman iney...@perceptron.com writes: Attached are couple smallish files (I suspect, CM_200909.bac might have just empty tables, no data - but it still produces an errror). Hmm. I get pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2741; 1259 30866 TABLE gp_cycle_200907 vec_dba pg_restore: [archiver (db)] could not execute query: ERROR: relation gp_cycle does not exist Command was: CREATE TABLE gp_cycle_200907 (CONSTRAINT gp_cycle_200907_cycle_date_time_check CHECK (((cycle_date_time = '2009-07-01 00:0... The tables all seem to inherit from tables you omitted from the dump, so of course it's not restorable for anyone else. Now I do see pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required) after that, but I'm wondering if this is just a problem in error recovery rather than the bug we thought we were looking for. regards, tom lane Right, like I mentioned, these are partitioned tables. Attached is script that could be used to pre-create parent tables (from which partitions were inherited). You run it before restoring backed up partition. Thank you for taking time to look into this issue. Regards, Igor Neyman parent_tables.sql Description: parent_tables.sql -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, June 22, 2010 2:41 PM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore Igor Neyman iney...@perceptron.com writes: Attached is script that could be used to pre-create parent tables (from which partitions were inherited). Thanks. Now that I dig into it, it looks like the actual trigger for the problem is that pg_dump, not pg_restore, couldn't seek while it was creating the dump file --- so it didn't seek back and update the file's table-of-contents with exact dump offsets. What command did you use to create the dump file, exactly? regards, tom lane Here is the backup script to backup all partitions for specific month (200907) in one backup file: SETLOCAL set PGPASSFILE=%PGINSTALL%\DB_scripts\postgres.pgpass SET PGBACKUPDRIVE=%PGBACKUP% pg_dump -U vec_dba -F c -f %PGBACKUPDRIVE%\PartitionedBackup\CM_200907.bac -v -Z 9 -t *200907 vector 2 %PGBACKUPDRIVE%\Backup\Log\DB_Backup.log ENDLOCAL This script is a part of bigger backup, which backs up other non-partitioned tables as well. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Oracle's Long data type equivalent data type in Postgresql ?
-Original Message- From: Achilleas Mantzios [mailto:ach...@matrix.gatewaynet.com] Sent: Friday, May 28, 2010 5:03 AM To: pgsql-admin@postgresql.org Subject: Re: Oracle's Long data type equivalent data type in Postgresql ? Στις Friday 28 May 2010 11:45:17 ο/η Ravi Katkar έγραψε: Hi , What is the Oracle's Long data type equivalent data type in Postgresql ? int8 i guess Regards, Ravi katkar -- Achilleas Mantzios No, it's bytea (and not int8). Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Live sort-of-'warehousing' database how-to?
-Original Message- From: Mario Splivalo [mailto:mario.spliv...@megafon.hr] Sent: Wednesday, March 31, 2010 10:20 AM To: pgsql-admin@postgresql.org Subject: Live sort-of-'warehousing' database how-to? Suppose I have 'stupid' database with just one tables, like this: CREATE TABLE messages ( message_id uuid NOT NULL PRIMARY KEY, message_time_created timestamp with time zone NOT NULL, message_phone_number character varying NOT NULL, message_state type_some_state_enum NOT NULL, message_value numeric(10,4) ) Now, let's say that I end up with around 1.000.000 records each week. I actually need just last week or two worth of data for the whole system to function normaly. But, sometimes I do need to peek into 'messages' for some old message, let's say a year old. So I would like to keep 'running' messages on the 'main' server, and keep there a month worth of data. On the 'auxiliary' server I'd like to keep all the data. (Messages on the 'auxiliary' server are in the final state, no change to that data will ever be made). Is there a solution to achieve something like that. It is fairly easy to implement something like INSERT INTO auxilary.database.messages SELECT * FROM main.database.messagaes WHERE message_id NOT IN (SELECT message_id FROM auxilary.database.messages) using python/dblink or something like that. But, is there already a solution that would do something like that? Or is there a better way to achieve desired functionality? Mike Partition your MESSAGES table by week or month (read on table partitioning in PG docs). Pg_dump old purtitions from current server, when they are not needed any more. Move backups of dumped partitions to your auxilary server, and pg_restore them there. Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] New/obsolete configuration parameters
Is there a document desribing (ar at least listing) new/obsolete configuration parameters in postgresql.conf, when upgrading from version to version? Regards, Igor Neyman
[ADMIN] relation between records in main and toast tables
Hello, Let's say TableA has toastable column, the contents of this column is stored in let's say pg_toast_1234. Is there a query to find which records (chunk_id, chunk_seq) in pg_toast_1234 store data for specific record in TableA (i.e. with PK column value eq. '567')? Igor Neyman
[ADMIN] excluding tables from VACUUM ANALYZE
When I run VACUUM ANALYZE (or vacuumdb -z) on the database, how can I exclude specific tables from being analyzed? Igor
Re: [ADMIN] can I update multiple table at a time?
If you could declare a Foreign Key on two columns in history table referencing master table, and declare this FK with ON UPDATE CASCADE option, then postgres will update status in history table for you whenever you update status in master table. Igor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jagadeesh Sent: Friday, October 10, 2008 1:12 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] can I update multiple table at a time? Hello Gurus, Is it possible to update two table using single sql statement? huh? let me be clear I have table master with state and date field. And in history table I have same fields i.e., state and date. I'm writing an update statement to set 'open' state records to 'closed' state after 7. both table can be joined using id. Any thoughts? Thanks -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Prent-Child relationships in SQL
Read the docs on connectby function provided by tablefunc contrib module. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gibson Chimhamhiwa Sent: Friday, July 25, 2008 7:43 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Prent-Child relationships in SQL Hi All, I have a simple table below and I want to be able to write a SQL select query that returns the grouping_ids until I get no more children. The parent_grouping_id column is the parent to the grouping_id child column. Can somebody please advise me how I can do this in postgreSQL. CREATE TABLE grouping ( grouping_id int4 NOT NULL, version int4, parent_grouping_id int4, topic_topic_id int4, CONSTRAINT grouping_pkey PRIMARY KEY (grouping_id), CONSTRAINT fk1e2e9d036852722d FOREIGN KEY (topic_topic_id) REFERENCES topic (topic_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk1e2e9d03a1bfbfe FOREIGN KEY (topic_topic_id) REFERENCES topic (topic_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk1e2e9d03cdaf7222 FOREIGN KEY (parent_grouping_id) REFERENCES grouping (grouping_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITHOUT OIDS; ALTER TABLE grouping OWNER TO napdba; Thanks In Advance.
Re: [ADMIN] Query a list of tables
First you get to Postgresql docs, from there you get anywhere you want. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gibson Chimhamhiwa Sent: Wednesday, July 23, 2008 11:10 AM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Query a list of tables Hi All, I have a postgreSQl database and just wan to know how I can get to its data dictionary.
Re: [ADMIN] dropping a user in 8.2.6
First do: DROP OWNED BY user1; then: drop role user1; Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rex Mabry Sent: Tuesday, July 08, 2008 12:45 PM To: pgsql-admin@postgresql.org Subject: [ADMIN] dropping a user in 8.2.6 I am using version 8.2.6 I created a role and granted all on table instrument in the database. But, when I tried to drop the role, I get the following error: mydb=# drop role system_dba; ERROR: role user1 cannot be dropped because some objects depend on it DETAIL: access to table mydb_admin.instrument Is there a way to drop a role with some sort of cascade option?
Re: [ADMIN] Postgres windows service shutdowns after start
stats_start_collector is an obsolete parameter in 8.3, that's why PG wouldn't start with it. Igor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of bogdad Sent: Wednesday, July 02, 2008 2:38 PM To: pgsql-admin@postgresql.org Subject: [ADMIN] Postgres windows service shutdowns after start Hi, I have a postgres 8.3 service working on win2003 server, it works fine. But, when i alter postgresql.conf adding the stats_starts_collector = on ( or even false, which bugs me to the end) the service starts and immedeately stops. If I revert the change, service works again. The event log is clear, the pg_log is empty. My login is local admin, and due to restrictions in environment, i currently cannot runas /user:postgres posmaster.exe to retrieve the stderr or stdout of postgres. So the questions: 1) Is it really connected to stats collector? [ the part in brackets makes me think it's not] 2) Maybe, there is a forsaken way for admin user to run postgres? 3) How to proceed? 4) Is there a way to syntax check .conf? (I know that I can send the .conf home and run postgres there, but don't like this option) Thanks in advance, Vladimir. -- View this message in context: http://www.nabble.com/Postgres-windows-service-shutdowns-after-start-tp1 8244024p18244024.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] dobc install error on Windows
I'm trying to install 8.3.1 (binaries) on Win2003 using windows installer (msi). I'm runnning silent install with: ADDLOCAL=server,psql,pgadmin,psqlodbc,nls and I'm getting error message saying, that psqlodbc is not included in the package. When I exclude odbc: ADDLOCAL=server,psql,pgadmin,nls install works fine. Is it true that odbc was excluded from 8.3.1 Windows distribution package? Should I change my scripts to run separate odbc install? I didn't have this problem with 8.2: odbc was in cluded along with everything else. Thanks in advance, Igor
[ADMIN] odbc install error on Windows
I'm trying to install 8.3.1 (binaries) on Win2003 using windows installer (msi). I'm runnning silent install with: ADDLOCAL=server,psql,pgadmin,psqlodbc,nls and I'm getting error message saying, that psqlodbc is not included in the package. When I exclude odbc: ADDLOCAL=server,psql,pgadmin,nls install works fine. Is it true that odbc was excluded from 8.3.1 Windows distribution package? Should I change my scripts to run separate odbc install? I didn't have this problem with 8.2: odbc was in cluded along with everything else. Thanks in advance, Igor
Re: [ADMIN] alter table serial-int
Eric,
Don't be so defensive.
Here is an abstract from documentation:
8.1.4. Serial Types
The data types serial and bigserial are not true types, but merely a
notational convenience for setting up unique identifier columns (similar
to the AUTO_INCREMENT property supported by some other databases). In
the current implementation, specifying
CREATE TABLE tablename (
colname SERIAL
);
is equivalent to specifying:
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
Thus, we have created an integer column and arranged for its default
values to be assigned from a sequence generator. A NOT NULL constraint
is applied to ensure that a null value cannot be explicitly inserted,
either. (In most cases you would also want to attach a UNIQUE or PRIMARY
KEY constraint to prevent duplicate values from being inserted by
accident, but this is not automatic.) Lastly, the sequence is marked as
owned by the column, so that it will be dropped if the column or table
is dropped.
Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no
longer automatic. If you wish a serial column to be in a unique
constraint or a primary key, it must now be specified, same as with any
other data type.
To insert the next value of the sequence into the serial column, specify
that the serial column should be assigned its default value. This can be
done either by excluding the column from the list of columns in the
INSERT statement, or through the use of the DEFAULT key word.
The type names serial and serial4 are equivalent: both create integer
columns. The type names bigserial and serial8 work just the same way,
except that they create a bigint column. bigserial should be used if you
anticipate the use of more than 231 identifiers over the lifetime of the
table.
The sequence created for a serial column is automatically dropped when
the owning column is dropped. You can drop the sequence without dropping
the column, but this will force removal of the column default
expression.
which proves that you don't understand serial data type.
Igor
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Erik Aronesty
Sent: Thursday, November 08, 2007 11:14 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] alter table serial-int
Erik Aronesty [EMAIL PROTECTED] writes:
for some odd reason when i try to change a table from serial to just
plain int with a default postgres seems to ignore me.
What PG version?
(PostgreSQL) 8.1.10
The fact that you even tried that suggests that you don't understand
very well what serial is. See the manual ...
I tried to change the data type first. Which implies I know what it
means.
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] Primary key
Yes, PG allows compound or composite keys for primary keys. Igor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Carol Walter Sent: Wednesday, September 19, 2007 10:37 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Primary key Hello, Does postgres allow compound or composite keys for primary keys? I thought I read someplace that it didn't. Now, I can't find a reference to it. Carol ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] help with query
Why not use information_schema? select prk.table_name AS PARENT_TABLE, prk.constraint_name AS PK, tc.table_name AS CHILD_TABLE, refc.constraint_name AS FK from information_schema.table_constraints prk, information_schema.referential_constraints refc, information_schema.table_constraints tc where prk.table_catalog = refc.unique_constraint_catalog and prk.constraint_type = 'PRIMARY KEY' and prk.constraint_name = refc.unique_constraint_name and tc.constraint_name = refc.constraint_name and tc.constraint_type = 'FOREIGN KEY' and tc.table_catalog = refc.constraint_catalog order by prk.table_name , tc.table_name; Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chris Hoover Sent: Thursday, August 16, 2007 11:19 AM To: pgsql-admin@postgresql.org Admin Subject: [ADMIN] help with query I need a little bit of help. I need to use sql to pull any tables that have the a foreign key referencing a given tables primary key. So far I have come up with the listed query. It works great for single column primary keys, but if a table has a multi column primary key, it is returning to many rows. How can I get it to work for tables with multi-column primary keys as well as single column primary keys? Thanks, Chris selecta.relname as table_name, c.attname as column_name, w.typname as domain_name frompg_class a, pg_constraint b, pg_attribute c, pg_type w wherea.oid = b.conrelid andc.atttypid = w.oid andc.attnum = any (b.conkey) anda.oid = c.attrelid andb.contype = 'f' anda.relkind = 'r' andc.attname in (selectz.attname frompg_class x, pg_constraint y, pg_attribute z wherex.oid = y.conrelid andz.attnum = any (y.conkey) andx.oid = z.attrelid andy.contype = 'p' andx.relname = 'table' ) ;
Re: [ADMIN] Delete COLUMN data
It is called update: UPDATE table_name SET column1 = NULL, column2 = NULL, column2 = NULL; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of smiley2211 Sent: Thursday, August 09, 2007 10:27 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Delete COLUMN data Hello all, I need to DELETE all the DATA from 3 columns in my table...what is the BEST way to handle this??? I don't want to DROP the columns just clear out ALL the data in those 3 fields... Thanks...Michelle -- View this message in context: http://www.nabble.com/Delete-COLUMN-data-tf4242917.html#a12073367 Sent from the PostgreSQL - admin mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Searching in a string with index
Index will not be used if your string has wild card (%) in the beginning. It should be used, if you change your query to: select * from tb_gen_person where pes_name like 'albert%'; Igor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alexander B. Sent: Wednesday, July 25, 2007 9:18 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Searching in a string with index Hi, When I search some words into a varchar column and this column has an index. Does Postgres use index?? The search is done this way: create table tb_gen_person ( pes_nro_doc numeric(14) not null, pes_name varchar(150) not null, constraint pk_person PRIMARY KEY(pes_nro_doc) ); create index ix_person_01 on tb_gen_person (pes_name); select * from tb_gen_person where pes_name like '%albert%'; Not considering upper case, or tsearch2, this type of search use index somehow? I overhear that if the search has more than 5 characters, probably would use index!! Is this true? Thanks ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Can primary key be dropped and added back in?
ALTER TABLE tableName ADD CONSTRAINT pkname_pkey (column1, column2, ...); From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jessica Richard Sent: Tuesday, July 17, 2007 2:44 PM To: pgsql-admin@postgresql.org Subject: [ADMIN] Can primary key be dropped and added back in? I have a huge table to load (30+M rows). Dropping indexes before loading will speed up my process, but I am not sure about the primary key that was created with the table creation. Is the Postgres primary key treated like an index? If yes, dropping the primary key might help my loading as well I can drop the primary key by alter table tbleName drop constraint pkname_pkey.. it dropped clean this way. But I haven't found the command to put the primary key back (all for testing now) Is there a command to add the primary key back after loading the data? Thanks Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games. http://us.rd.yahoo.com/evt=48224/*http://sims.yahoo.com/
Re: [ADMIN] Postgres VS Oracle
This document: http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html could answer some of your questions. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Tokmatchi Sent: Monday, June 18, 2007 11:55 AM To: [EMAIL PROTECTED]; pgsql-admin@postgresql.org; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [ADMIN] Postgres VS Oracle Hello from Paris I am DBA for Oracle and beginner on Postgres. For an company in France, I must make a comparative study, between Postgres and Oracle. Can you send any useful document which can help me. Scalability ? Performance? Benchmark ? Availability ? Architecture ? Limitation : users, volumes ? Resouces needed ? Support ? Regards cordialement david tokmatchi +33 6 80 89 54 74
Re: [ADMIN] copying data into another database ? (replication)
Look up dblink(...) function in PG documentation: http://search.postgresql.org/search?u=%2Fdocs%2F8.2%2Fstatic%2Fq=dblink Igor Neyman From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Raul Retamozo Sent: Wednesday, June 06, 2007 7:09 PM To: pgsql-admin@postgresql.org Subject: [ADMIN] copying data into another database ? (replication) Hi everyone on the list. I've been trying to get a way to copy a postgresql table or some columns from table into another one, but this new table must be in another databse and, also, could be in another server. I was working on a jdbc app , which run well when the destiny table was in the same database than the source ( it was with create table as select ... into)), really easy, but we have the other case. I feel it could be possible writing a function , which receives some variables ( postgresql server, db and table, sql query for filtering data). The problem is that I cant find any example about the following: -- connecting to a server within the function. -- retrieve data form this server ( I think some cursors would be useful to save each column, and run by the rows) -- connecting to another server, und create a table (with a name provided by user), where I can insert data from cursors. I think it could be answered by replication (Slony ? ), but I have to do it without any other tool. Does anyone knows If it's possible, and how to do it? thanks in advance. raul n. retamozo velarde [EMAIL PROTECTED] Consigue el nuevo Windows Live Messenger Pruébalo http://get.live.com/messenger/overview
Re: [ADMIN] Change order of table-columns in pg_catalog.pg_attribute.attnum
More important question would be, why would you want to do this (change columns order)? I can't think of any valid reason for this. Igor Neyman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Erwin Brandstetter Sent: Wednesday, June 06, 2007 11:22 AM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Change order of table-columns in pg_catalog.pg_attribute.attnum On Jun 6, 4:59 pm, [EMAIL PROTECTED] (Tom Lane) wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Erwin Brandstetter escribió: If I want to change the default order of two columns of a table, can I just manipulate the values in pg_catalog.pg_attribute.attnum? It works -- as long as the table is empty. And as long as you have no views, foreign keys, indexes, defaults, rules, etc etc etc referencing the columns. Short answer is don't even think of trying it. Thanks for your answers. I had tried it with data in the table and it seemed to work, but it does mess up views referencing the table. So, no go. The only way to change the default order of columns is still to drop the table and all references to it and recreate it all? Regards Erwin ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] anonymous block in Postgres - Hello World
Coming from Oracle world, I also was missing the ability to execute anonymous blocks. So I wrote this function: CREATE OR REPLACE FUNCTION exec_plpgsql_block(exec_string text) RETURNS BOOLEAN AS $THIS$ DECLARE lRet BOOLEAN; BEGIN EXECUTE 'CREATE OR REPLACE FUNCTION any_block() RETURNS VOID AS $$ ' || exec_string || ' $$LANGUAGE PLPGSQL;' ; PERFORM any_block(); RETURN TRUE; END; $THIS$LANGUAGE PLPGSQL; to which I pass my anonymous block as a parameter. As you can see, this function creates/replaces on the fly function any_block() and executes it. Pretty simple solution. Igor Neyman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, May 24, 2007 11:12 AM To: Abraham, Danny Cc: pgsql-admin@postgresql.org; Devrim GÜNDÜZ Subject: Re: [ADMIN] anonymous block in Postgres - Hello World Abraham, Danny [EMAIL PROTECTED] writes: This code is my first like Oracle anonymous blocl. It does not go = through. There are no anonymous blocks in Postgres --- you must create a function. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Copying schemas between databases
Lookup dblink in postgres documentation. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, May 09, 2007 10:00 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Copying schemas between databases Hi Im using postgresql 8.2 I need to access data accross databases, I believe this is not possible in Postgre. So my only alternative appears to be copying data, the whole public schema, from one database to another. For example, i have a reporting database and several other databases i would like to report on. I need to copy my public schemas from the original databases to the reporting database and rename them like so: MyFirstDatabase.public ReportingDatabase.firstdb OtherDatabaseReportingDatabase.otherdb What is the easiest way to accomplish this? Or am i wrong in thinking i cannot access adta directly accross databases? Any help much apperciated. thanks gp
Re: [ADMIN] exception handling in postgres plpgsql
Obviously, you are coming from Oracle world. In PG according to: http://www.postgresql.org/docs/current/static/errcodes-appendix.html exception WHEN no_data THEN ... Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Karthikeyan Sundaram Sent: Tuesday, April 03, 2007 6:35 PM To: pgsql-admin@postgresql.org; pgsql-sql@postgresql.org Subject: [ADMIN] exception handling in postgres plpgsql Hi, I am having a function like this create or replace function audio_format_func ( in p_bitrate audio_format.audio_bitrate%TYPE, in p_sampling_rate audio_format.sampling_rate%type, in p_bit_per_sample audio_format.bit_per_sample%type, in p_audio_codec audio_format.audio_codec%type, in p_mimetype audio_format.mimetype%type, in p_mono_stero audio_format.number_of_channel%type) returns int as $$ DECLARE p_audio_id audio_format.audio_id%type; begin select audio_id into a from audio_format where audio_bitrate = p_bitrate and sampling_rate = p_sampling_rate and mimetype = p_mimetype and number_of_channel = p_mono_stero and audio_code = p_audio_codec; return 1; exception when NO_DATA_FOUND then return 100; end; $$ language 'plpgsql'; When I compile, I am getting an error message ERROR: unrecognized exception condition no_data_found CONTEXT: compile of PL/pgSQL function audio_format_func near line 15 How will I handle exceptions in postgres? Please advise. Regards skarthi i'm making a difference. Make every IM count for the cause of your choice. Join Now. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http: //im.live.com/messenger/im/home/?source=wlmailtagline
Re: [ADMIN] problem upgrading from 8.1.6 to 8.1.8 --- relation tablename does not exist
Why wouldn't you look at the definition of the view which is based on this table (and which you claim is still working), and how it references basic table? M.b. it'll point you to the actual table location. Igor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Glen W. Mabey Sent: Friday, March 09, 2007 12:01 PM To: Tom Lane Cc: PostgreSQL Admin Mailing List Subject: Re: [ADMIN] problem upgrading from 8.1.6 to 8.1.8 --- relation tablename does not exist On Fri, Mar 09, 2007 at 11:41:23AM -0500, Tom Lane wrote: Glen W. Mabey [EMAIL PROTECTED] writes: When I SELECT any records from a certain table (Acquisitions), I get: acqlibdb= select * from Acquisitions; ERROR: relation Acquisitions does not exist acqlibdb= select * from public.Acquisitions; ERROR: relation public.Acquisitions does not exist Maybe it's not in the public schema but some other one (implying you forgot about a nondefault search_path setting). Humm. I don't think so, since I've never altered the search_path settings, nor used any schema besides public. And I've been working with this database for 8 months now without ever encountering such an error. And all of the other tables work just fine. Is there some way to force an integrity check of the entire database? Thank you for your reply. Glen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] host name?
Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor
Re: [ADMIN] host name?
And one more question. Is there a way (function/view) to find machine name on which user program (connected to Postgres) runs? Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Neyman Sent: Monday, February 26, 2007 10:29 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] host name? Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor
Re: [ADMIN] host name?
You are right, wrong question. What I really want to know is formulated in my next message. Which is: based on program (connected to PG) name find the machine name it runs on. For those familiar with Oracle, it's program, machine columns in v$session view. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andy Shellam (Mailing Lists) Sent: Monday, February 26, 2007 10:56 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] host name? Igor Neyman wrote: Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor Surely you need to know this to connect to it in the first place?
Re: [ADMIN] host name?
Right, I looked at pg_stat_activity, but besides having ip address (instead of machine name), it doesn't have clent program name connected to PG. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ray Stell Sent: Monday, February 26, 2007 12:53 PM To: Igor Neyman Cc: Andy Shellam (Mailing Lists); pgsql-admin@postgresql.org Subject: Re: [ADMIN] host name? not sure how to resolve the name, but maybe this is close enough: select usename, client_addr from pg_catalog.pg_stat_activity; On Mon, Feb 26, 2007 at 11:47:13AM -0500, Igor Neyman wrote: You are right, wrong question. What I really want to know is formulated in my next message. Which is: based on program (connected to PG) name find the machine name it runs on. For those familiar with Oracle, it's program, machine columns in v$session view. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andy Shellam (Mailing Lists) Sent: Monday, February 26, 2007 10:56 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] host name? Igor Neyman wrote: Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor Surely you need to know this to connect to it in the first place? -- You have no chance to survive make your time. ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] host name?
Right, I looked at pg_stat_activity. But besides having ip address (instead of machine name), it doesn't have clent program name connected to PG. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ray Stell Sent: Monday, February 26, 2007 12:53 PM To: Igor Neyman Cc: Andy Shellam (Mailing Lists); pgsql-admin@postgresql.org Subject: Re: [ADMIN] host name? not sure how to resolve the name, but maybe this is close enough: select usename, client_addr from pg_catalog.pg_stat_activity; On Mon, Feb 26, 2007 at 11:47:13AM -0500, Igor Neyman wrote: You are right, wrong question. What I really want to know is formulated in my next message. Which is: based on program (connected to PG) name find the machine name it runs on. For those familiar with Oracle, it's program, machine columns in v$session view. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andy Shellam (Mailing Lists) Sent: Monday, February 26, 2007 10:56 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] host name? Igor Neyman wrote: Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor Surely you need to know this to connect to it in the first place? -- You have no chance to survive make your time. ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
