Re: [ADMIN] Installing multiple instances of Postgred on one FreeBSD server

[Jim Mercer]

On Thu, May 02, 2013 at 10:10:51AM +0300, Achilleas Mantzios wrote:
> Hi, i was just lurking around,
> being previously interested in running pgsql in jail environment (must have 
> been some years back)
> i noticed that jails had issues with running many instances of postmaster 
> listening on the same port
> and with the same user (say e.g. postgres). So basically having cloned jails 
> running pgsql was not
> out of the box possible without tweaking either user or port.
> Being many years out of freebsd admin, has this been remedied by now?

yeah, those issues are no longer

the key thing is to have things running on the base machine set to listen
on a specific IP, rather than all interfaces, as many applications do by
default.

so, for instance, for ssh on the base system, you would do:

sshd_enable="YES"
sshd_flags="-o ListenAddress=120.121.122.123"

this will prevent it from listening for ssh on the ip you intend to use for
the jail.

and in the jail, you can leave it listen on all interfaces, since, in the
jail, it only has its own interface.

in the case of pgsql, the best way to do it is to not have pgsql installed 
on the base server, and only run it in a jail (or a couple jails, if you are
looking to smooth the upgrade from one to the next).

if you want to run pgsql on the base server, then set:
listen_addresses = '120.121.122.123'
in postgresql.conf, and there will be no conflict of listeners.

it issue of users/uids is moot, since the jail is independent of the main 
system.

assuming you configure the base system to listen only on its own IP, installing
on the jail, is no different than installing on a normal system.

--jim

> 
> On ?? 01 ?? 2013 16:24:01 Jim Mercer wrote:
> > On Wed, May 01, 2013 at 02:21:44PM -0600, Benjamin Krajmalnik wrote:
> > > So essentially we would have to run jails on this - does that affect
> > > performance?
> > 
> > i would say minimal impact.
> > 
> > > 
> > > 
> > > -Original Message-
> > > From: Jim Mercer [mailto:j...@reptiles.org] 
> > > Sent: Wednesday, May 01, 2013 2:20 PM
> > > To: Benjamin Krajmalnik
> > > Cc: pgsql-admin@postgresql.org
> > > Subject: Re: [ADMIN] Installing multiple instances of Postgred on one
> > > FreeBSD server
> > > 
> > > On Wed, May 01, 2013 at 01:58:21PM -0600, Benjamin Krajmalnik wrote:
> > > > I would like to install PG 9.2 on the same server and use Slony to 
> > > > replicate the databases, and once everything is up take the 9.0 
> > > > cluster down.
> > > 
> > > allocate a new ip to the server.
> > > reconfigure/restart 9.0 to listen on the main IP (not "all" as default)
> > > 
> > > use ezjail to create a freebsd jail on thenew IP
> > > 
> > > install 9.2 in the jail
> > > 
> > > treat it them as two servers.
> > > 
> > > point clients at new ip.
> > > 
> > > once migrated, delete all 9.0 from the main server.
> > > 
> > > if you need to upgrade again, add another jail.
> > > 
> > > stop and delete the old jail whe the next upgrade is complete.
> > > 
> > > repeat as often as you want to upgrade.
> > > 
> > > i'm pretty sure this can be adapted to linux, but i forget what their
> > > jail equivilent is.
> > > 
> > > the overhead is minimal
> > > 
> > > 
> > > 
> > 
> > 
> -
> Achilleas Mantzios
> IT DEV
> IT DEPT

-- 
Jim Mercer Reptilian Research  j...@reptiles.org+1 416 410-5633
"He who dies with the most toys is nonetheless dead"


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] Migration from Oracle 11g to Postgresql

[Silvana Bravo]

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.

Re: [ADMIN] Migration from Oracle 11g to Postgresql

[Igor Neyman]

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] Migration from Oracle 11g to Postgresql

[Prashanth Ranjalkar]

Yes, You can use the mentioned method by using scripts or also there are
few migration tools available to perform the migration of schema..


**
*Thanks & Regards,*
*** *
*Prashanth Ranjalkar*
 *Database Consultant & Architect*
*Email:prashant.ranjal...@gmail.com*
*Skype:prashanth.ranjalkar*
*www.postgresdba.net*


On Thu, May 2, 2013 at 7:27 PM, Silvana Bravo wrote:

> 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.
>
>

Re: [ADMIN] Migration from Oracle 11g to Postgresql

[Vibhor Kumar]

On May 2, 2013, at 9:57 AM, Silvana Bravo  wrote:

> 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.


You can use Migration Tool kit or xDB Replication solution from Oracle to 
PostgreSQL. 
http://www.enterprisedb.com/docs/en/9.2/repguide/Postgres_Plus_xDB_Replication_Server_Users_Guide-51.htm#P6096_508028

Thanks & Regards,
Vibhor Kumar
Blogs:http://vibhork.blogspot.com
http://vibhorkumar.wordpress.com



-- 
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

[Leonardo Carneiro]

I used full convert to convert firebird DBs to postgresql. with a few
tweaks, works like a charm. never tried with oracle.


On Thu, May 2, 2013 at 11:09 AM, Vibhor Kumar  wrote:

>
> On May 2, 2013, at 9:57 AM, Silvana Bravo  wrote:
>
> > 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.
>
>
> You can use Migration Tool kit or xDB Replication solution from Oracle to
> PostgreSQL.
>
> http://www.enterprisedb.com/docs/en/9.2/repguide/Postgres_Plus_xDB_Replication_Server_Users_Guide-51.htm#P6096_508028
>
> Thanks & Regards,
> Vibhor Kumar
> Blogs:http://vibhork.blogspot.com
> http://vibhorkumar.wordpress.com
>
>
>
> --
> 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

[Paul B. Anderson]

There is also ora2pg.  I have not tried it but it looks easy. 
http://ora2pg.darold.net/


On 5/2/13 10:18 AM, Leonardo Carneiro wrote:
I used full convert to convert firebird DBs to postgresql. with a few 
tweaks, works like a charm. never tried with oracle.



On Thu, May 2, 2013 at 11:09 AM, Vibhor Kumar > wrote:



On May 2, 2013, at 9:57 AM, Silvana Bravo mailto:bravo.sil...@gmail.com>> wrote:

> 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.


You can use Migration Tool kit or xDB Replication solution from
Oracle to PostgreSQL.

http://www.enterprisedb.com/docs/en/9.2/repguide/Postgres_Plus_xDB_Replication_Server_Users_Guide-51.htm#P6096_508028

Thanks & Regards,
Vibhor Kumar
Blogs:http://vibhork.blogspot.com
http://vibhorkumar.wordpress.com



--
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

[Simpson, John (IMS)]

I used Ora2Pg to migrate our production databases from Oracle 10g to Postgres a 
couple years ago. It was extremely simple.

http://ora2pg.darold.net/

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.




Information in this e-mail may be confidential. It is intended only for the 
addressee(s) identified above. If you are not the addressee(s), or an employee 
or agent of the addressee(s), please note that any dissemination, distribution, 
or copying of this communication is strictly prohibited. If you have received 
this e-mail in error, please notify the sender of the error.

Re: [ADMIN] Migration from Oracle 11g to Postgresql

[McKinzie, Alan (Alan)]

I used ora2pg a couple years ago.  I made a few minor changes to fit our 
specific requirements, but it worked well for our migration to Postgresql.

Alan

From: pgsql-admin-ow...@postgresql.org 
[mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Paul B. Anderson
Sent: Thursday, May 02, 2013 9:30 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Migration from Oracle 11g to Postgresql

There is also ora2pg.  I have not tried it but it looks easy.  
http://ora2pg.darold.net/
On 5/2/13 10:18 AM, Leonardo Carneiro wrote:
I used full convert to convert firebird DBs to postgresql. with a few tweaks, 
works like a charm. never tried with oracle.

On Thu, May 2, 2013 at 11:09 AM, Vibhor Kumar 
mailto:vibhor@gmail.com>> wrote:

On May 2, 2013, at 9:57 AM, Silvana Bravo 
mailto:bravo.sil...@gmail.com>> wrote:

> 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.

You can use Migration Tool kit or xDB Replication solution from Oracle to 
PostgreSQL.
http://www.enterprisedb.com/docs/en/9.2/repguide/Postgres_Plus_xDB_Replication_Server_Users_Guide-51.htm#P6096_508028

Thanks & Regards,
Vibhor Kumar
Blogs:http://vibhork.blogspot.com
http://vibhorkumar.wordpress.com



--
Sent via pgsql-admin mailing list 
(pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] Migration from Sybase to Postgresql

[Dev Kumkar]

Hello Everyone,

A similar related topic is being discussed and tools are suggested for
Oracle DB to Postgresql migration.

Here my objective is migration of Sybase DB to Postgresql.

Again stored procedures, triggers and functions will be manual approach.
For schema part please provide suggestions.

Thanks..

Re: [ADMIN] Migration from Oracle 11g to Postgresql

[Vibhor Kumar]

On May 2, 2013, at 10:38 AM, "McKinzie, Alan (Alan)"  wrote:

> I used ora2pg a couple years ago.  I made a few minor changes to fit our 
> specific requirements, but it worked well for our migration to Postgresql.

Ora2Pg is a good tool for one time migration. 

However, when we come to crucial production databases where we have least 
downtime window. Then replicating data from Oracle to PostgreSQL and later 
doing switchover to postgreSQL (new database) in short window, is the efficient 
way I have found and I have used xDB Replication most of time, since its easy 
to configure and provides GUI mode to monitor your replication etc. I did many 
migration from Oracle Production database to PostgreSQL. Getting long downtime 
window for Production is the main issue I have seen for crucial customer facing 
applications.

Thanks & Regards,
Vibhor Kumar
Blogs:http://vibhork.blogspot.com
http://vibhorkumar.wordpress.com



-- 
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 Sybase to Postgresql

[Dev Kumkar]

On Thu, May 2, 2013 at 8:12 PM, Dev Kumkar  wrote:

> Hello Everyone,
>
> A similar related topic is being discussed and tools are suggested for
> Oracle DB to Postgresql migration.
>
> Here my objective is migration of Sybase DB to Postgresql.
>
> Again stored procedures, triggers and functions will be manual approach.
> For schema part please provide suggestions.
>
> Thanks..
>

[ADMIN] Copy one database to another server

[Huan Ruan]

Hi All

We are migrating from Firebird to Postgres. One task we frequently perform
is to copy one database from one server to another. It's easy to do in
Firebird as a database can basically be treated as a file (it shouldn't be
I know). This appears to be harder in Postgres, especially we will have
multiple databases in one Postgres cluster.

The goal is to be able to copy one database from one server to another
quickly; and the two servers may host different number of databases. The
main database is 400GB of size and some down time is acceptable.

We looked into a few options but none of them looks exactly optimal to our
current practice. Any suggestions will be appreciated.


   1. backup/restore. Easy to do and requires no downtime, but slow.
   2. pg_basebackup. Easy to do and requires no downtime, but it copies the
   whole cluster; and requires archive mode on?
   3. create a new db using the current one as a template. Easy to do and
   speed is acceptable. A bit downtime is ok, but it's only on the current
   server. Is there a way to move it to anther server?

If I make sure there are no connectiosn to a database, is it safe to just
copy that one folder of files for that database? If so, how can I merge
that into another cluster on another server?

In order to use pg_basebackup, another thought was to run multiple
instances of Postgres on each server, so copying one database is the same
as copying the whole cluster. Will there be much performance hit with this
approach?

Thanks in advance.

Cheers
Huan

Re: [ADMIN] Copy one database to another server

[Steve Crawford]

On 05/02/2013 03:01 PM, Huan Ruan wrote:

Hi All

We are migrating from Firebird to Postgres. One task we frequently 
perform is to copy one database from one server to another...
I think it would be helpful to know *why* you do this (backup, 
forensics, development, shifting load among virtual machines or ???) as 
well as a better idea of how often you do this, how long it takes and 
the amount of downtime you consider acceptable. That way we may be 
better able to come up with a way of solving the actual problem you are 
facing.


There are a number of non-core replication solutions such as Slony and 
many can migrate a single database or even specific table(s).


Cheers,
Steve



--
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] Copy one database to another server

[bricklen]

On Thu, May 2, 2013 at 3:01 PM, Huan Ruan  wrote:

> create a new db using the current one as a template. Easy to do and speed
> is acceptable. A bit downtime is ok, but it's only on the current server.
> Is there a way to move it to anther server?


FWIW, I recently set up a 160GB clone by simply piping from pg_dump over
the network into psql, alleviating the need to dump to disk. In my case,
the server did not have enough space to hold the pg_dump, so I had to
stream it.

pg_dump -Fp your_db | psql -h new_server -U postgres your_new_db

You could also search the archives for caveats and other (and safer) ways
to do this.