Re: [ADMIN] transaction error handling
On Tue, 29 Nov 2011 09:57:24 -0800, Kasia Tuszynska wrote: Hi Everybody, This is an architectural question. I am testing on Postgres 9.0.2 on windows and linux(suse, rhel, ubuntu) I want to make sure that I have the correct understanding of the Postgres architecture and would like to enquire if there are any plans to change it. Comparing Oracle and Postgres from the perspective of error handling on the transaction level I observed the following: Oracle: Begin transaction Insert - no error Implicit savepoint Insert - error raised Implicit rollback to the savepoint, no transaction loss, error raised on the insert statement that errored out. End transaction, implicit commit, with the single error free insert. Postgres: Begin transaction Insert - no error Insert - error raised Transaction loss = no implicit rollback to the single error free insert. Is this a correct interpretation of the Postgres transaction error handling? If so, are there any changes being considered, or perhaps already implemented? I suspect you may be barking up the wrong tree. Comparing default behaviour of PSQL to SQL*Plus is not the same thing as comparing PostgreSQL to Oracle. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Re: Convert / Migrate From Oracle 11gR2 To PostgreSQL ? On CentOS 5.7 x86_64
On Wed, 07 Dec 2011 09:13:10 -0500, Gene Poole wrote: This has to do with my personal home environment. I'm running Oracle 11gR2 DBMS with 4 instances using a single home directory. For each of the instances I'm using LVM file systems with 10 logical volumes defined (/dbmsu00 used for the install and /dbmsu01 - u09 for the required files created using the DBCA utility). A single LISTENER created using the NETCA utility. I chose Oracle because back in the dark ages I was a Oracle DBA when Oracle 7.3 was current. I've been told that it's OK to download and use the Oracle DBMS in a noncommercial environment for a limited time - well it's reached the end of that time. I can't use the free Oracle XE because the database size limitations (4 GB). Based upon what I've read and seen, the best choice for me is PostgreSQL Community Edition. What I need is advice, documentation, and information on how to take the above and move it from Oracle to PostgreSQL along with updating my JBoss 6.0 environment so it has access to the PostgreSQL databases/instances? Can someone supply me with some direction? I use PostgreSQL at home from *choice* (I am an Oracle DBA at work). But I don't think there is any time limit on personal non-commercial use of Oracle EE (you may want to check the OTN Licence Agreement on their download page). PostgreSQL is FOSS, so there are no Editions, by the way. Lastly, a couple of polite requests: Please do not post HTML, and please consider carefully before crossposting to multiple groups. -- 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] bad block problem
On Wed, 07 Dec 2011 21:22:05 +, jkells wrote: I do not have a recent backup of this database/table Any help would be appreciated. Here's some help: Next time you establish a database, set up and test the backup regime. We hear this tale of woe time and time again. I have *no* sympathy. -- 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] bad block problem
On Wed, 07 Dec 2011 22:20:30 +, jkells wrote: I am relying on identifying and correcting a bad block. Well, good luck with that. Most of the time you can't. Just check your disk, replace it if necessary, restore from your backup and roll forward. Oh, you can't do that, since you didn't bother to back up. Never mind. -- 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] Drop Schema from Postgres
On Thu, 05 Jan 2012 10:22:59 -0600, Kevin Grittner wrote: Tripura k_trip...@hotmail.com wrote: I am trying to drop a schema by writing the following line DROP SCHEMA xxx IF EXISTS The syntax requires: DROP SCHEMA IF EXISTS xxx; I have two issues here - 1 I am not sure how to specify the db from which the schema should be deleted You connect to a particular database. All operations are against that database. 2 I am getting the following error message -- DROP: command not found Perhaps you should try this in an interactive session and paste the actual error lines. I get this: ERROR: syntax error at or near IF LINE 1: DROP SCHEMA xxx IF EXISTS; He's not even in psql -- 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] apt installation postgresql 8.3
On Mon, 16 Jan 2012 23:24:41 +0300, David M. Gullever wrote: Question - user postgres If the admin is creating various postgresql users to work on different databases - is it advisable to create these as LINUX users so that they have a home directory for saving their scripts - and while we are about it - where would experienced postgresql admins (as user postgres - no home directory, although the set command reveals HOME=/var/lib/postgresql ???) save their scripts - and just one more thing... this is the second time that I have installed postgresql 8.3 - the first time I had lots of trouble just logging user postgres on to the database engine, and finally solved the problem with a PATH entry into /etc/profile. This time I just do su postgres and follow with psql (from any directory) and Bobs yrrr uncle - I'm in - but I cannot find a PATH entry for postmaster, so how does this work - thanks dragonfish ??? Ps - since it is not possible to logon to the system as user postgres - is it generally recommended that user postgres be given a password immediately after installation - I would think yes ??? That's a silly question - I take it back !!! You are going about this entirely the wrong way, and would be well advised to have a serious rethink about what it is you are trying to achieve. User postgres does *not* need to log in to Unix/Linux directly, and therefore should *not* be assigned a password. For occasional tasks like editing postgresql.conf or pg_hba.conf, it may be convenient to su to the postgres account rather than doing it as root. It may of course be used to stop/start PostgreSQL if root doesn't do it; Use cases will vary. You should be looking to minimise use of the postgres account, rather than the opposite. Have people do things (whether PostgreSQL superusers or not) under their own Unix/Linux accounts: - $ sudo su - postgres -bash-4.2$ whoami postgres -bash-4.2$ psql misc psql :FATAL: no pg_hba.conf entry for host [local], user postgres, database misc, SSL off -bash-4.2$ logout $ whoami walterh $ psql misc psql (9.1.2) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type help for help. misc=# \q $ createuser anotherdba Shall the new role be a superuser? (y/n) ^C $ - -- 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] Is it possible to create a CHECK constraint for my use case?
On Tue, 31 Jan 2012 13:41:00 +0530, Gnanakumar wrote: Hi, Our Production server is running PostgreSQL v8.2.22 on CentOS5.2. We want to enable a specific CHECK constraint for our application table as explained below: There are 2 columns in the table: managertype numeric(1) and managerid numeric(10). managertype accepts only 2 valid values, either '1' or '2'. managertype |managerid - 1 | null values NOT allowed 2 | null values allowed - We want to enable a CHECK constraint based on 2 columns data in the table in such a way that if managertype is 1, then null values are not allowed in managerid column. In other words, I want to create a CHECK constraint something like this: IF (managertype = 1) THEN managerid IS NOT NULL. Is it possible to create a CHECK constraint for my use case explained above? Production server? Bollocks. It's homework. Look it up. -- 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 backup ( logical using pg_dump) using AMANDA
On Mon, 13 Feb 2012 10:57:39 -0800, Dinesh Bhandary wrote: Hello, Has anyone done postgres logical backup (pg_dump) using Amanda ( Advanced Maryland Automatic Network Disk Archiver). So far I have seen plugin for wall logging method, but have not found any information on pg_dump. Is it possible to call a script that does pg_dump and move a dump file to the backup destination using AMANDA. Any help or workable solution is greatly appreciated. I've never heard of AMANDA, but I don't understand the problem. The output of pg_dump is just a file, albeit usually a large one. -- 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] table names seem identical
On Wed, 07 Mar 2012 10:39:48 -0500, Ray Stell wrote: how I can differentiate these: oamp=# \z public.c3* Access privileges Schema | Name | Type | Access privileges | Column access privileges +---+---+--- +-- public | c3p0_connection_test | table | | public | c3p0_connection_test | table | | (2 rows) oamp=# select * from pg_tables where tablename = 'c3p0_connection_test'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers +--++ ++--+- public | c3p0_connection_test | admin || f | f| f (1 row) misc=# create table test1(flag varchar(1)); CREATE TABLE misc=# create table test1 (flag varchar(1)); CREATE TABLE -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL
On Wed, 14 Mar 2012 13:24:36 +0200, Khangelani Gama wrote: thanks, the issue we have is that we have many Linux users having root access into the system. So they're able to access the DB by just going in as su - superusername. If this user is able to make any updates inside the database it might create problems. Auditors wants PostgreSQL to tell who updated what inside the database besides client users that access the database from outside using some applications. So this common superusername doesn't tell the actual person who got into the system and went onto make updates inside the database because they first logged as their linux users before as going in as postgres user called superusername. http://dl.dropbox.com/u/6106778/message.jpg -- 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] Unending DB migration
On Mon, 16 Apr 2012 16:27:38 -0700, ЇЯЅHAÐ wrote: Hi, We are trying to migrate from Postgres to Oracle, although our DB is gigantic but we started on Mar 14th 2012 its still running with an average of 40 records per day 400,000? That's pathetic. Why didn't you just use Postgres's copy to CSVS and then Oracle's SQL*Loader? How big is gigantic? How can we check what is going wrong. Any memory leak, CPU issue or anything. Please help me fix it Hi,brbrWe are trying to migrate from Postgres to Oracle, although our DB is giganticbrbut we started on Mar 14th 2012 amp; its still running with an average of 40 records per daybrbrHow can we check what is going wrong.br Any memory leak, CPU issue or anything.brbr clear=allPlease help me fix itbrbrbr Please don't post in HTML. -- 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] Canot access PostgreSQL via psql -h (Vmware Fusion)
On Fri, 24 Aug 2012 10:35:54 -0400, Tom Lane wrote: No route to host doesn't have anything to do with whether there's anything listening on the target port ... what's more likely is that the OP mistyped the IP address, or else there's something wrong with his network layout, interface netmask assignments, that sort of thing. I don't know about VMWare, but with VirtualBox if you set up two guests with NAT they are sharing the host's IP address as far as the outside world is concerned. So each can see the host and the outside world, but they can't see one another. -- 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] Windows Services and Postgresql 9.1.3
On Fri, 21 Sep 2012 10:33:25 -0600, Scott Marlowe wrote: On Tue, Sep 11, 2012 at 2:48 PM, Vincent Dautremont vinc...@searidgetech.com wrote: Hi, I'm using Postgresql 9.1.3 x64 on Windows 2008, I'm doing reliability tests and effects and consequences of a server power failure. If you're looking to test for reliability and bugginess, I'd highly suggest testing against the latest minor version of 9.1.5. If reliability of interest, why is OP using Windows? -- 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] Pg 8.3.x on RHEL 6.3?
On Wed, 10 Oct 2012 14:15:15 -0700, Rosser Schwarz wrote: Fellow Pg Admins, At $work, we're preparing to move one of our Pg instances to new hardware, and considering an OS upgrade at the same time. The instance in question is running an older version of 8.3, which we'd like to upgrade to 8.3.latest (presently .21; unf., we don't have the time or testing resources to migrate to a more recent major release of postgres as well). In preparation for that effort, I've been asked to avail myself of the collective experience and wisdom of the wider community to see if anyone has seen anything unexpected, or any kind of misbehavior in running 8.3 on RHEL 6.3. None of my research suggests we should have any problems, but for sake of due diligence and checking the box, I'm soliciting any input anyone with experience with this configuration might be able to offer. We aren't doing anything that would subject us to the referential_constraints issue, fixed in 8.3.17. Can we expect to see any benefit from the llseek kernel changes with 8.3, or is the upside of that more limited to people also running 9.2? OK, I'll do your work for you. $200/hr. -- 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] migrate from PostgreSQL to Oracle
On Thu, 25 Oct 2012 15:29:29 +0200, jo wrote: Hi all, I'm working with same db schema in PostgreSQL and Oracle, We mainly work in PostgreSQL but sometimes we need to copy schema and data from pg to oracle because some our customers want to use oracle instead of pg. Thus I'm looking for some linux script to migrate from pg to oracle. At the moment, I dump data from pg using pg_dump in the format: INSERT INTO table (columns) (values) then I load it into the Oracle db using cx_Oracle this procedure is so slow, and sometimes I have to edit and modify data manually, because some INSERT format aren't compatible. Is there any interesting linux script to do this more easily? something like the ora2pg script. If the schemata are truly the same, I'd use 'copy to' a CSV file on the Postgres side, then SQL*Loader to put it into Oracle. Easily scriptable. For maximum speed, use 'copy to on the server side; *not* psql, and (assuming that the data is clean), direct path for SQL*Loader. -- 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] Question about access to db server
On Sat, 08 Dec 2012 18:01:06 +, T.T Le wrote: Hello, We are running PostgresPlus 8.4AS on a linux system. I don't need to edit the config files or to restart the clusters (the DBA will do that for us). I only need access to help troubleshoot performance issues with the databases. My question is: would pgAdmin be sufficient for what i need to do? From what I understand, i can check the status of the database, view the postgresql logs, the postgresql.conf and pg_hba.conf. The DBA should do that job. -- 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] Migración
On Thu, 03 Jan 2013 16:24:17 +, Alcívar Arcos wrote:
(Quoted in full)
Postgres gentlemen good day, my question is very timely, I need to
migrate a system from Informix, PostgreSQL, what are the advantages
Postgres, and is there any tool to perform this migration?
thanks
Alcivar Arcos
html
head
style!--
.hmmessage P {
margin:0px;
padding:0px }
body.hmmessage {
font-size: 12pt;
font-family:Calibri }
--/style/head
body class='hmmessage'div dir='ltr'span id=result_box
lang=enspan class=hpsPostgres/span span
class=hpsgentlemen/span span class=hpsgood day/spanspan,
my/span span class=hpsquestion is very/span span
class=hpstimely/spanspan, I need/span span class=hpsto
migrate/span span class=hpsa system from/span span
class=hpsInformix/spanspan,/span span
class=hpsPostgreSQL/spanspan, what are/span span
class=hpsthe advantages/span span
class=hpsPostgres/spanspan,/span span class=hpsand is there
any/span span class=hpstool to/span span class=hpsperform
this migration/spanspan?brnbsp;brthanks
brbr/span/spanAlcivar Arcosbr
/div/body
/html
1) Please do9n't post HTML.
2) Did you try a web search engine before asking this?
--
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] Possible database corruption - urgent
On Mon, 07 Jan 2013 14:35:48 -0700, Benjamin Krajmalnik wrote: snip What do you think you will gain by adding urgent to your subject line? What do you think you will gain by posting in HTML? -- 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] Possible database corruption - urgent
On Mon, 07 Jan 2013 14:35:48 -0700, Benjamin Krajmalnik wrote: snip What do you think you will gain by adding urgent to your subject line? What do you think you will gain by posting in HTML? -- 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] Failover question
On Mon, 22 Apr 2013 12:06:52 -0600, Benjamin Krajmalnik wrote: I currently have 2 servers running PostgreSQL 9.0 under FreeBSD configured with streaming replication. DB1 is the primary server, and DB2 is the replicated server, which is used for all of the read only queries. I am having some hardware issues on DB1 which is affecting its performance, resulting in data getting queued up. I would like to change roles on these servers, such that DB1 becomes the replicated server and DB2 the master server. This is a production system, so I cannot take any chances of anything going wrong. What is the correct procedure for accomplishing this? The correct procedure is to have a test server and test standby so that you can test. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
