[GENERAL] PostgreSQL 9.1.10 release date?
I was wondering what is the expected release data for the next PostgreSQL patch - presumably 9.1.10? Although there is no documentation stating release timeframes I'd become accustomed to them being released in the first week of the month every other month. Thank You Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-9-1-10-release-date-tp5760391.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_basebackup questions
Hi, PostgreSQL 9.1.6 Linux (SLES 11) i have a pg_basebackup job that is executed within a script. My command is: /postgresql/v91/bin/pg_basebackup -h 127.0.0.1 -D /postgresql/pg_backup/backupdir -Ft -Z 5 The result of this creates 2 files: base.tar and 16450.tar I understand that 16450 is the oid of the additional tablespace i created however this tablespace resides within the data directory so as far as i can tell (and have tested) suggests this is a duplicate. I did a test restore and was able to recover all tablespaces from the base.tar without any issues - confirming my suspicion that 16450.tar is essentially an uneeded duplicate backup. What pg_basebackup command would i run to avoid created of 16450.tar ? Another thing is that during pg_basebackup i get warning: pg_basebackupWARNING: could not read symbolic link "pg_tblspc/mytablespacename": Invalid argument Again based on my successful recovery i'm assuming this is not a major issue but it is still a little disconcerting. Thanks Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-basebackup-questions-tp5734371.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgagent linux install issues
Hi, I'm attempting to install pgagent on SLES 11. So far I have downloaded the pgagent (3.3.0) to the server. Extracted using gunzip and untarred. I had the sysadmin install cmake 2.6 & wxGTK & wxGTK-devel using yast. When i run wx-config it shows version 2.8.10 When i attempt to install pgagent I execute cmake /path/to/source I get error: CMake Error at cmake/FindWX.cmake:271 (MESSAGE): The selected wxWidgets configuration (version: 2.8, debug: yes, static: no, unicode: yes, modules: base) is not available. Call Stack (most recent call first): CMakeLists.txt:95 (FIND_PACKAGE) -- Configuring incomplete, errors occurred! I've also ran ccmake /path/to/source with following options: CMAKE_BUILD_TYPE CMAKE_INSTALL_PREFIX /usr/local PG_CONFIG_PATH /postgresql/v91/bin/pg_config STATIC_BUILD OFF WX_CONFIG_PATH /usr/bin/wx-config WX_DEBUG Please can someone help as to what I am missing, the error certainly suggests it is an issue with WxWidgets dependency but I'm not sure what that issue is? Thanks Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/pgagent-linux-install-issues-tp5733982.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL logging - restrict error messages
I'm running PostgreSQL 9.1.6 on Linux SLES 11 SP2 My question is, is it possible to restrict entries into the log bases on number of entries per second or avoid duplicate entries within the same second? Some background: My non default logging parameters in postgresql.conf #LOGGING log_directory='/postgresql/pg_log' logging_collector='ON' log_line_prefix='%t %r %u %d %a' , log_statement='ddl' log_destination='stderr,csvlog' log_connections=on As you can see I'm doing some fairly verbose logging. This is intentional, it gives me good auditing capability and helps spot errors in applications. The volume in logs is mostly manageable however if a developer runs a statement presumably in a cursor loop that has an error in it then the logs can quickly get big and they are simply repeats of the same information e.g. 2012-11-16 08:39:32 ip hsf_web_user pgdev [unknown]ERROR: current transaction is aborted, commands ignored until end of transaction block STATEMENT: INSERT INTO hse.extract_lpt (etc) I have the above in the log thousands of times.. and within a minute or 2 it is having to rotate logs. It is useful to know that there is an error on the insert into hse.extract_lpt however I don't need to know every occurence of this (particularly multiple instances within a second). Thank You Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-logging-restrict-error-messages-tp5732480.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_archivecleanup - clean up files with extensions
I'm running postgresql9.1.6 I would like to run pg_archivecleanup on wal files that have a .gz extension. I notice in the version 9.2 documentation there is an option for this. Is there an easy way to achieve the same thing in 9.1? Thanks, Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-archivecleanup-clean-up-files-with-extensions-tp5729965.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] oracle_fdw
Thanks Laurenz for your post...
Some more info
Oracle Server:Oracle 11g R2 (11.2.0.2.0)
Client: 11.2
Was installed using Oracle Universal Installer
I don't really want to post the full environment of the postmaster but
basically I could see no entry in there for ORACLE_HOME or TNS_ADMIN, should
I?
LD_LIBRARY_PATH=mypostgreshomedirectory/lib
are there any others in particular of interest?
Here is my fdw, server and foreign table specs. I have 'myinstancename'
defined in tnsnames.ora which is in $ORACLE_HOME/NETWORK/ADMIN
CREATE FOREIGN DATA WRAPPER oracle_fdw
HANDLER oracle_fdw_handler
VALIDATOR oracle_fdw_validator;
ALTER FOREIGN DATA WRAPPER oracle_fdw
OWNER TO postgres;
CREATE SERVER myinstancename
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver 'myinstancename');
ALTER SERVER myinstancename
OWNER TO postgres;
CREATE FOREIGN TABLE public.wild_lek
("WL_ID" integer ,
"WL_ALIAS" character varying(50) ,
"WL_AHM_FL" character varying(1) ,
"WL_INACTIVE_FL" character varying(1) ,
"WL_SATELLITE_FL" character varying(20) ,
"WL_LESPPSG_FL" character varying(1) )
SERVER myinstancename
OPTIONS (table 'MYUSER.MYTABLE');
ALTER FOREIGN TABLE 'MYUSER.MYTABLE' OWNER TO postgres;
Rob
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/oracle-fdw-tp5728931p5729005.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] oracle_fdw
Environment: PostgreSQL 9.1.6 SLES 11 SP2 Oracle_fdw 0.9.7 I am trying to implement the use of oracle_fdw. So far I have installed an oracle client on my postgres server. I can connect to the oracle environment from the postgres server (as postgres os user) by navigating to $ORACLE_HOME/bin then ./sqlplus /nolog then: connect user@instance I have downloaded and run make and make install for the oracle_fdw - both executed successfully. I have created the extension successfully. I have created a foreign server, foreign data wrapper and a foreign table. When i try a select from the foreign table I get: ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle DETAIL: ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle SQL state: HV00N from what limited info i can find this is most likely due to my ORACLE_HOME environment variable or other environment setting? I have set ORACLE_HOME in postgres user .bash_profile Where should i set ORACLE_HOME and TNS_ADMIN environment variables? Is there something else I am missing? Does the database require to be restarted following any changes to environment variables? Thank you Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/oracle-fdw-tp5728931.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sequence and Schema permissions information schema
PostgreSQL v9.1.6 Are sequence and schema permissions documented anywhere in the information schema. I've looked through documentation and the information_schema itself but have had no luck. I noticed in the 9.2 documentation there is a reference to 'sequences' in information_schema.usage_privileges. I also noticed there is a has_schema_privilege and has_sequence_privilege function so I'm assuming this data is somewhere? Thanks Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/Sequence-and-Schema-permissions-information-schema-tp5726514.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL force create table / ignore constraints?
Version Postgresql 9.1.6 OS: SLES 11 64 bit Background: Our developers create database schema in development environment using PGAdmin (often using the GUI to generate the DDL). We always deploy to production using a script, a single .sql file which we execute via psql command line. This allows us to generate an output with any errors and have good view of deployment history over time. Issue The issue we have is that developers generate the .sql script mainly by copying and pasting from PGAdmin's SQL pane. The issue we have is then the order of the object creation is important otherwise creation of tables and fail when there is a foreign key constraint on another table that does not exist (but is created later in the script). This is not a big deal in a schema with 3 or 4 tables but when there are 20+ it is time consuming task to reorder all the create statements. Can anyone recommend a way of dealing with this? My only other thought has been pg_dump although i would prefer if the developers could generate the scripts themselves. Thanks, Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-force-create-table-ignore-constraints-tp5726469.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL data loads - turn off WAL
My version: PostgreSQL v9.1.5 Version string: "PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit" Basically my question is: Is there currently any way to avoid wal generation during data load for given tables and then have point in time recovery after that? Background and blurb The situation i'm referring to here is for a development environment. I require point in time recovery because if there is crash etc I don't want to lose up to a days work for 12 developers. I'm fairly new to PostgreSQL so please forgive any gaps in my knowledge. A developer did a data load yesterday of approximately 5GB of data into a new schema. This generated approximately 7GB of wal. The situation arises where if something is incorrect in the data load the data load may need to be repeated 2 or 3 times (thus generating 20GB +of WAL). For a data load i don't want wal to be generated. I accept the fact before there was nothing and from the point of the next pg_basebackup there was everything. It is from the point i say ok that is everything (the next backup) that i want point in time recovery to apply to that table. It is doesn't seem practical, and appears very risky to turn off wal_archive during the data load. I'd appreciate your thoughts and suggestions, Thanks, Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-data-loads-turn-off-WAL-tp5725374.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql Developer Privileges
Postgresql v9.1.4 SUSE Linux Enterprise Server SP2 In my agency we have application developers who do most of the database design/development themselves (not my choice) in the development environment but do not administer the database. Therefore I want developers to be able to create & drop tables, create & drop indexes, views, triggers, procedures sequences etc. But I don’t want them to be able to create login roles or shutdown the database or drop schemas. Basically they need to be a “power user” but definitely not a superuser. The main issue I’m having in Postgresql is that I can give them permission to create objects in a schema but not drop objects if they are not the owner. I don’t want all developers to use a shared login role or have access to a superuser account. Also I don’t think it makes sense for the developers to really own the object themselves. Is there a way to achieve the following? Basically each developer has their own login role Developers can create and drop objects in schemas in which they have create privileges granted. Developers can drop objects created by other developers Developers do not own the objects themselves Thank You Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-Developer-Privileges-tp5718244.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres maintenance db
I'm using postgres 9.1.4 on Suse Linux Enterprise Server 11. After successful installation I by default have one database installed called postgres. I'm starting the process of migrating some database schemas off Oracle and mysql onto postgres but I want to understand how to best set up the "databases". What is the purpose of the postgres database? I try and drop it and get "maintenance database can't be dropped" error. Should I create a separate database that has all my application schemas in it and let the postgres database be stand-alone, or should I put my application schemas inside the postgres database? I didn't really want my database to be called postgres, can it be renamed? Thank you Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-maintenance-db-tp5718134.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
