Re: [GENERAL] pg_catalog tables don't have constraints?

[Neil Anderson]

That's a great start, thanks! You should put a pointer to that page in the Pg 
wiki.

I think the 1 thing that would really help it though is to show the actual
connection/relations of the columns. For example, a line between pg_index
and pg_class is not quite as informative as a line from pg_index.indexrelid
to pg_class.oid (something the PNG file in the image that Thomas pointed
out does). I suspect that's a limitation of the tool not your desire.


I think that might be possible, I may have turned off the labels on the 
edges.




Is this process automated, or does it require you to lay it out?



The layouts were all built in the DataGrip luckily!


In my perfect world, :) there would be a way to generate it automatically with
Graphviz or something similar. Hmm, perhaps an idea to pursue when I can find
some time.

Kevin




I think SchemaSpy's new output looks really interesting and uses 
Graphviz. After doing the work to add the relationships I realised that 
SchemaSpy does not like the oid system columns that we use for keys so 
it wouldn't work. I will go ahead and replace them all with something 
else and see how it does but I haven't gotten back to it yet.



--
Neil Anderson
n...@postgrescompare.com
http://www.postgrescompare.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] pg_catalog tables don't have constraints?

[Neil Anderson]

>
> Of course. My plan is to copy the catalogs into a new schema so I can add fk
> constraints and then get something like navicat or datagrip to draw it.
>

Hi. I made some progress on this and I've added all the diagrams and
documentation I've been able to produce so far for v10beta as well as
the tools used here:

https://www.postgrescompare.com/2017/06/11/pg_catalog_constraints.html

I would like to try SchemaSpy but it doesn't seem to like constraints
on oid columns too much so I'll have to work around it somehow. Might
be worth it though since it seems to have a new lease of life at
schemaspy.org

Neil


-- 
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] pg_catalog tables don't have constraints?

[Neil Anderson]

On 9 Jun 2017 14:59, <kbran...@pwhome.com> wrote:

Neil Anderson <n...@postgrescompare.com> wrote:

> I've been exploring the pg_catalog tables and pointed a couple of tools at
> it to extract an ER diagram for a blog post. At first I thought it was a
bug
> in the drawing tool but it appears that the relationships between the
> pg_catalog tables are implicit rather than enforced by the database, is
that correct?

Every time I have to dive into the pg_* tables, I really want such a
diagram because
the relationships aren't obvious to me, so I've been looking for a diagram
like that
and haven't found one. I've also considered trying to make one, but with
all of the
custom types, my normal ERD tool isn't very cooperative.

Would you be willing to share the diagram with the list when you're done?

Would you be willing to share the names of the tools and process you used
to create the diagram?


Of course. My plan is to copy the catalogs into a new schema so I can add
fk constraints and then get something like navicat or datagrip to draw it.



Thanks,
Kevin


--
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] pg_catalog tables don't have constraints?

[Neil Anderson]

> The bigger picture here is that catalog changes are supposed to be
> executed by C code in response to DDL commands, and it's the C code
> that is charged with maintaining catalog consistency.  Constraints
> would be useful if we supported updating the catalogs with direct
> SQL manipulations; but we don't really.  You can do that, if you're
> a superuser who's willing to take risks, but our policy is that if
> you break the catalogs that way you get to keep both pieces.

Ah! That makes sense. Constraints are there to help you when inserting
rows and when it comes to the catalogs you shouldn't be doing that
directly. I think for the diagram I'll add the links manually with the
caveat that they are not constraints, just edges in a graph.

Thanks for the details Tom!



-- 
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_catalog tables don't have constraints?

[Neil Anderson]

Hi,

I've been exploring the pg_catalog tables and pointed a couple of
tools at it to extract an ER diagram for a blog post. At first I
thought it was a bug in the drawing tool but it appears that the
relationships between the pg_catalog tables are implicit rather than
enforced by the database, is that correct?

For example, pg_class has relnamespace which according to the
documentation refers to pg_namespace.oid
(https://www.postgresql.org/docs/current/static/catalog-pg-class.html),
yet there is no FK there as far as I can see. Maybe I am missing
something or there is an interesting story as to why?

Thanks,
Neil


-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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] dump to pg

[Neil Anderson]

>
> Thanks for the suggestion. Problem is the data is highly sensible and
> cannot go on the cloud or non trusted place

Sounds like the real question now is not how to import the data, but
how to convert the backups you have to CSV or similar?
Another idea for SQL Server is to use the bcp utility.
https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/


contact
Description: Binary data

-- 
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 database into PostgreSQL using Ora2PG tool.

[Neil Anderson]

On 2 June 2017 at 11:57, stevenchang1213  wrote:
>
>
> tell me where this function add_job_history() is?
> Actually, I don't think you can count on ora2pg to transform your pl/sql
> code to plpgsql or other (un)trusted procedural language code. It's not that
> simple!

I wonder, does plpgsql compilation check for existence of the
add_job_history function or is that a runtime check?

> you can type "\df  add_job_history"  in psql session to check it's existence
> if it belongs to public schema or access it using fully qualified name
> scheme.
>
>
> Steven
>
<>
-- 
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] dump to pg

[Neil Anderson]

On 1 June 2017 at 17:37, Nicolas Paris <nipari...@gmail.com> wrote:
>> If they aren't too big, you might get away by installing the express edition 
>> of the respective DBMS, then import them using the native tools, then export 
>> the data as CSV files.

Good idea. I think SQL Server Express is limited to 10GB on the later
versions. Another tool that can read SQL Server backups is SQL Data
Compare from Redgate, it has a 14 day trial.

http://www.red-gate.com/products/sql-development/sql-data-compare/


Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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 database into PostgreSQL using Ora2PG tool.

[Neil Anderson]

On 2 June 2017 at 04:16, PAWAN SHARMA <er.pawanshr0...@gmail.com> wrote:
> Hi All,
>
> I am migrating Oracle database into PostgreSQL using Ora2PG tool.
>
> So, I am facing one issue with trigger after generating script output of
> Oracle database.
>
> Oracle :
>
> CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY
> AFTER UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES
> FOR EACH ROW
> BEGIN
>   add_job_history(:old.employee_id, :old.hire_date, sysdate,
>   :old.job_id, :old.department_id);
> END;
> /
>
> The script generated by Ora2PG tool.
>
> DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
> CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger
> AS $BODY$
> BEGIN
>   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
>   OLD.job_id, OLD.department_id);
> RETURN NEW;
> END
> $BODY$
>  LANGUAGE 'plpgsql';

The examples here
https://www.postgresql.org/docs/9.5/static/sql-createfunction.html
have a semi colon after 'END', is that the syntax error?

> CREATE TRIGGER update_job_history
> AFTER UPDATE ON employees FOR EACH ROW
> EXECUTE PROCEDURE trigger_fct_update_job_history();
>
>
> when I try to run the above-generated script it will show below error.
>
> ERROR:  syntax error at or near "add_job_history"
> LINE 4:   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES...
>   ^
> NOTICE:  relation "employees" does not exist, skipping

Since this is a NOTICE maybe it's a red herring? the results of a
CREATE IF NOT EXISTS or similar?


-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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 to PostgreSQL Migration.

[Neil Anderson]

>>>
>>>
>> ya, but how can fix it.??
>>
>> because memory is not an issue on both the server.
>>
>

I've never used ora2pg but there is some documentation saying that
often an out of memory issue can be fixed by adjusting your DATA_LIMIT
setting. https://github.com/darold/ora2pg/blob/master/README#L307

> Hi Chris,
>
> I am able to get the output script in output.sql but now data migration is
> the big problem.

-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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] Help with terminology to describe what my software does please?

[Neil Anderson]

>> Cluster comparison would only occur if you have two or more clusters on
>> the same server, although it's possible to compare across servers,
>
>
> Explain, because as I understand it a server = one cluster:
>

I think he was using server in the server=one machine sense, ie a
single machine/server can have multiple clusters/database servers.

> https://www.postgresql.org/docs/9.6/static/app-pg-ctl.html
>
> "The init or initdb mode creates a new PostgreSQL database cluster. A
> database cluster is a collection of databases that are managed by a single
> server instance. This mode invokes the initdb command. See initdb for
> details."
>
>> but that would involve a lot more work. AFAIK, the only differences for a
>> cluster would be:
>> 1. PostgreSQL version
>> 2. path to database
>> 3. database users (note: it is also possible to make users database
>> specific)
>> 4. list of defined databases
>
>
> And anything different below the above, I am thinking checking a dev cluster
> against a production cluster.
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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] Help with terminology to describe what my software does please?

[Neil Anderson]

>
>
> Cluster comparison would only occur if you have two or more clusters on
> the same server, although it's possible to compare across servers,
> but that would involve a lot more work. AFAIK, the only differences for a
> cluster would be:
> 1. PostgreSQL version
> 2. path to database
> 3. database users (note: it is also possible to make users database
> specific)
> 4. list of defined databases
>

I was considering configuration settings to be at the cluster level too.
Stuff from pg_settings or pg_config. Also I think tablespaces are at that
level too. What do you think?


> Database comparison would involve db names, owners, encodings, tablespaces
> and acl's
> You might also want to include sizes. You can use the following two
> queries to help
> with that
>
> SELECT db.datname,
>au.rolname as datdba,
>pg_encoding_to_char(db.encoding) as encoding,
>db.datallowconn,
>db.datconnlimit,
>db.datfrozenxid,
>tb.spcname as tblspc,
>db.datacl
>   FROM pg_database db
>   JOIN pg_authid au ON au.oid = db.datdba
>   JOIN pg_tablespace tb ON tb.oid = db.dattablespace
>  ORDER BY 1;
>
> SELECT datname,
>pg_size_pretty(pg_database_size(datname))as size_pretty,
>pg_database_size(datname) as size,
>(SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
>   FROM pg_database)  AS total,
>((pg_database_size(datname) / (SELECT SUM(
> pg_database_size(datname))
>FROM pg_database) ) *
> 100)::numeric(6,3) AS pct
>   FROM pg_database
>   ORDER BY datname;
>

That's a great idea! Thanks for the info.


>
>

>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.com

[GENERAL] Help with terminology to describe what my software does please?

[Neil Anderson]

Hi,

I'm working on a tool that can compare the properties of Postgres
objects from different instances, finding the differences and
outputting the update SQL.

It can compare objects that are defined at the cluster, database or
schema level. As such I'm finding it difficult to describe what the
tool does simply and accurately. I've tried 'compares PostgreSQL
schemas' but that doesn't capture the database and cluster parts,
'compares PostgreSQL schema and database objects'. That sort of thing.
Right now I have a mix of terms on my website and I would prefer to
tighten it up.

I guess I don't know what is the most common way to say that it
compares everything but the data. Any suggestions from your
experience?

Thanks,
Neil


-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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] Any undocumented catalog changes remaining for v10?

[Neil Anderson]

>
> Feature freeze is in effect and we have a Beta release out.  Major bugs or
> major usability issues are the only things that would cause any change to
> the catalogs at this point and there likely won't be many, if any, of those
> (at least not ones that necessitate catalog changes - must discoveries at
> this point end up being implementation details).
>
> David J.

That makes sense and is what I was hoping for! Thanks for the info.



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

[GENERAL] Any undocumented catalog changes remaining for v10?

[Neil Anderson]

Hi all,

I am writing a blog post about changes to the catalogs between v9.6.3
and the upcoming v10. I've diffed the catalogs.sgml and it looks
pretty informative so far, see
https://gist.github.com/sql-migrate/8e87214cc3127ba52fc93a589f040425.

I'm not too familiar with the release cycle and whether or not I can
expect a lot of changes to the docs between now and release time for
v10?

Thanks

-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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] column names and dollar sign

[Neil Anderson]

>> "Armand Pirvu (home)" <armand.pi...@gmail.com> writes:
>>> Ran into the following statement
>>
>>> CREATE TABLE test(
>>>  Date$ date,
>>>  Month_Number$ int,
>>>  Month$ varchar(10),
>>>  Year$ int
>>> );

A strange naming convention. It has a whiff of Visual Basic Type
Characters about it,
https://docs.microsoft.com/en-us/dotnet/articles/visual-basic/programming-guide/language-features/data-types/type-characters.

Any chance there is a connection there?

-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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]

[Neil Anderson]

>
> Like I said, what I expect to see from the query is:
>
> id | integer | | 5| 2 | 0 | P |
> name | varchar | 50| 2 | | | | 
>
> So I need the information about the field and whether the field is a
> primary/foreign key or not.
>

I had a go at it using the catalog tables from v9.5 and an example
table 'films', maybe you can extend this further to get what you need
from the pg_attribute, pg_class, pg_type and pg_constraint tables?

SELECT columns.attname as name,
data_types.typname as type,
columns.attlen as length,
columns.attnotnull as not_null,
constraints.contype
FROM pg_attribute columns
INNER JOIN pg_class tables ON columns.attrelid = tables.oid
INNER JOIN pg_type data_types ON columns.atttypid = data_types.oid
LEFT JOIN pg_constraint constraints
ON constraints.conrelid = columns.attrelid AND columns.attnum = ANY
(constraints.conkey)
WHERE tables.relname = 'films' AND columns.attnum > 0;

Thanks,
Neil

-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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] Python versus Other Languages using PostgreSQL

[Neil Anderson]

On 9 May 2017 at 06:20, Neil Anderson <neil.t.ander...@gmail.com> wrote:
> On 9 May 2017 at 05:26, Francisco Olarte <fola...@peoplecall.com> wrote:
>> Paul:
>>
>> On Tue, May 9, 2017 at 2:45 AM, Paul Hughes <p...@vivation.com> wrote:
>>> My question still remains though - why is it that all the largest web 
>>> platforms that have used PostgreSQL *specifically* choose Python as their 
>>> back-end language?
>>
>> Do you have any data supporting that? AFAIK people tend to choose the
>> language first, database second, not the other way round, and many
>> times the platform language is nailed, but the db can be changed.
>> Also, WHICH platforms are you referring to?
>
> Well put. So far I've worked with Flask, Pylons, Rails and ASP.net.
> All have an ORM layer (SQLAlchemy, ActiveRecord, EntityFramework) with
> support for several database technologies. The framework* is specific
> and fixed but can pull data from anywhere.

*The language is specific and fixed but the data can come from anywhere.

>
>>
>>> Why are Postgres and Python so married, in the same way that Node.js is 
>>> largely married to MondogDB?
>>
>> I do not think either of these is true.
>>
>> Francisco Olarte.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general


-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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] Python versus Other Languages using PostgreSQL

[Neil Anderson]

On 9 May 2017 at 05:26, Francisco Olarte  wrote:
> Paul:
>
> On Tue, May 9, 2017 at 2:45 AM, Paul Hughes  wrote:
>> My question still remains though - why is it that all the largest web 
>> platforms that have used PostgreSQL *specifically* choose Python as their 
>> back-end language?
>
> Do you have any data supporting that? AFAIK people tend to choose the
> language first, database second, not the other way round, and many
> times the platform language is nailed, but the db can be changed.
> Also, WHICH platforms are you referring to?

Well put. So far I've worked with Flask, Pylons, Rails and ASP.net.
All have an ORM layer (SQLAlchemy, ActiveRecord, EntityFramework) with
support for several database technologies. The framework is specific
and fixed but can pull data from anywhere.

>
>> Why are Postgres and Python so married, in the same way that Node.js is 
>> largely married to MondogDB?
>
> I do not think either of these is true.
>
> Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] Python versus Other Languages using PostgreSQL

[Neil Anderson]

I'm also interested to know which frameworks you're referring to?

Regarding Node and Mongo I imagine that they are well suited because
Mongo stores JSON documents and Node, being Javascript, has first
class support for JSON. Python and PostgreSQL's relationship might be
more of a principled one? Pythonic being a characteristic not
dissimilar to stable, reliable and of high quality.

On 8 May 2017 at 21:22, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 05/08/2017 05:45 PM, Paul Hughes wrote:
>>
>> Thank you for the links. I'm glad there are other languages that are
>> working with PostgreSQL. My question still remains though - why is it that
>> all the largest web platforms that have used PostgreSQL *specifically*
>> choose Python as their back-end language? Why are Postgres and Python so
>> married, in the same way that Node.js is largely married to MondogDB?
>
>
> I think you are going to have name frameworks, because AFAIK Drupal uses
> PHP, Ruby on Rails uses Rails and so on:
>
> https://en.wikipedia.org/wiki/Comparison_of_web_frameworks
>
> As to why Postgres and Python seem to be attached I would say that is
> because about the time people started looking for an alternative to
> MySQL/PHP, Python reached the state and breadth of distribution to became
> the language to pair with Postgres.
>
>>
>>
>> On Mon, May 8, 2017 at 3:50 PM, Adrian Klaver <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 05/08/2017 02:26 PM, Paul Hughes wrote:
>>
>> Hello,
>>
>> I noticed that most of the largest web platforms that use
>> PostgreSQL as
>> their primary database, also use Python as their primary back-end
>> language. Yet, according to every benchmark I could find over
>> the last
>> couple of years, back-end languages like PHP, HHVM, and Node.JS
>> outperform Python by 2x to 8x!
>>
>>
>> Postgres does not really care what you use to pull data from it.
>> There are many libraries across many languages that you can use.
>>
>>
>>
>>
>>
>> <https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail_term=icon>
>> Virus-free. www.avast.com
>> <https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail_term=link>
>>
>> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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] Stored procedure version control

[Neil Anderson]

On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote:

Elsewhere, somebody was asking how people implemented version control
for stored procedures on (MS) SQL Server.

The consensus was that this is probably best managed by using scripts or
command files to generate stored procedures etc., but does anybody have
any comment on that from the POV of PostgreSQL?



I can't comment from the POV of those who represent Postgres, but I used 
to work for a company who specialised in change management for database 
products, SQL Server and Oracle in particular. There are at least two 
approaches. The migrations approach and the state based approach.


For migrations you create up and down scripts/code fragments to move the 
database through versions over time, committing them to a source control 
system as you go. Usually the database will contain some tables to keep 
track of the current live version.


With the state based approach you just store the DDL for each object in 
the source control system. You can see how an object changes over time 
by just inspecting one file. You can automate the scripting process or 
use one of the diffing tools that supports comparing to DDL directly.


State based handles merge conflicts better than migrations. Migrations 
handles data changes better than state based. Migrations also is better 
if you are deploying to multiple production databases that may all be on 
different versions.


If your database contains a lot of logic or you have a large distributed 
team you are more likely to have merge issues and so state based is 
probably the better choice. Smaller team, less logic and a production 
environment where you need to be able to update from any version 
reliably? Migrations is a good choice.


Additionally you don't have to stick with one or the other. In the early 
days while you have little data to worry about you might use the static 
approach and then switch to migrations. You just pick a baseline to 
start from and carry on from there.


--
Neil Anderson
n...@postgrescompare.com
http://blog.postgrescompare.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] Stored procedure version control

[Neil Anderson]

On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote:

Elsewhere, somebody was asking how people implemented version control
for stored procedures on (MS) SQL Server.

The consensus was that this is probably best managed by using scripts or
command files to generate stored procedures etc., but does anybody have
any comment on that from the POV of PostgreSQL?



I can't comment from the POV of those who represent Postgres, but I used 
to work for a company who specialised in change management for database 
products, SQL Server and Oracle in particular. There are at least two 
approaches. The migrations approach and the state based approach.


For migrations you create up and down scripts/code fragments to move the 
database through versions over time, committing them to a source control 
system as you go. Usually the database will contain some tables to keep 
track of the current live version.


With the state based approach you just store the DDL for each object in 
the source control system. You can see how an object changes over time 
by just inspecting one file. You can automate the scripting process or 
use one of the diffing tools that supports comparing to DDL directly.


State based handles merge conflicts better than migrations. Migrations 
handles data changes better than state based. Migrations also is better 
if you are deploying to multiple production databases that may all be on 
different versions.


If your database contains a lot of logic or you have a large distributed 
team you are more likely to have merge issues and so state based is 
probably the better choice. Smaller team, less logic and a production 
environment where you need to be able to update from any version 
reliably? Migrations is a good choice.


Additionally you don't have to stick with one or the other. In the early 
days while you have little data to worry about you might use the static 
approach and then switch to migrations. You just pick a baseline to 
start from and carry on from there.


--
Neil Anderson
n...@postgrescompare.com
http://blog.postgrescompare.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] Re: regarding schema only migration from sqlserver to postgres with runmtk.sh

[Neil Anderson]

On 2016-06-16 11:42 AM, Durgamahesh Manne wrote:

i got another error even i specified -targetdbtype
like
./runMTK.sh -sourcedbtype sqlserver -targetdbtype postgresql
-targetSchema public -schemaOnly -allTables dbo
Running EnterpriseDB Migration Toolkit (Build 49.0.4) ...
Source database connectivity info...
conn =jdbc:jtds:sqlserver://fxserver.trustfort.com:49883/DataFeedHandler
<http://fxserver.trustfort.com:49883/DataFeedHandler>
user =trustfort
password=**
Target database connectivity info...
conn =jdbc:postgresql://192.168.168.201:5432/raghu
<http://192.168.168.201:5432/raghu>
user =postgres
password=**
Connecting with source SQL Server database server...
Connected to Microsoft SQL Server, version '10.50.1600'
Connecting with target Postgres database server...
Exception in thread "main" java.lang.NoClassDefFoundError:
org/postgresql/Driver
at
com.edb.dbhandler.postgresql.PGConnection.(PGConnection.java:32)
at com.edb.common.MTKFactory.createMTKConnection(MTKFactory.java:228)
at
com.edb.MigrationToolkit.createNewTargetConnection(MigrationToolkit.java:5987)
at com.edb.MigrationToolkit.initToolkit(MigrationToolkit.java:3376)
at com.edb.MigrationToolkit.main(MigrationToolkit.java:1700)
Caused by: java.lang.ClassNotFoundException: org.postgresql.Driver





On Thu, Jun 16, 2016 at 8:59 PM, Neil Anderson
<neil.t.ander...@gmail.com <mailto:neil.t.ander...@gmail.com>> wrote:

On 2016-06-16 11:13 AM, Durgamahesh Manne wrote:

yes sir

as per above discussion..i already looked pdf postgres plus
guide you
mentioned

as well as i already set password for postgres user associated with
postgres db

On Thu, Jun 16, 2016 at 8:30 PM, Durgamahesh Manne
<maheshpostgr...@gmail.com <mailto:maheshpostgr...@gmail.com>
<mailto:maheshpostgr...@gmail.com
<mailto:maheshpostgr...@gmail.com>>> wrote:

hi
sir
as per above discussion same error repeated even mentioned ip
address of hostname

./runMTK.sh -sourcedbtype sqlserver -targetSchema public
-schemaOnly
-allTables dbo


TARGET_DB_URL=jdbc:postgresql://192.168.168.201:5432/raghu
<http://192.168.168.201:5432/raghu>
<http://192.168.168.201:5432/raghu>
TARGET_DB_USER=postgres
TARGET_DB_PASSWORD=*



Connecting with source SQL Server database server...
Connected to Microsoft SQL Server, version '10.50.1600'
Connecting with target EnterpriseDB database server...
MTK-10045: The URL specified for the "target" database is
invalid.
Check the connectivity credentials.
Stack Trace:
com.edb.MTKException: MTK-10045: The URL specified for the
"target"
database is invalid.
Check the connectivity credentials.

On Thu, Jun 16, 2016 at 7:55 PM, David G. Johnston
<david.g.johns...@gmail.com
<mailto:david.g.johns...@gmail.com>
<mailto:david.g.johns...@gmail.com
<mailto:david.g.johns...@gmail.com>>> wrote:

On Thu, Jun 16, 2016 at 10:19 AM, Durgamahesh Manne
<maheshpostgr...@gmail.com
<mailto:maheshpostgr...@gmail.com>
<mailto:maheshpostgr...@gmail.com
<mailto:maheshpostgr...@gmail.com>>>wrote:


sir

PostgreSQL always no1 in world
as per above discussion

migration not working from sql to postgreSQL  with
runmtk.sh


Connecting with source SQL Server database server...
Connected to Microsoft SQL Server, version '10.50.1600'
Connecting with target EnterpriseDB database server...
MTK-10045: The URL specified for the "target"
database is
invalid.
Check the connectivity credentials.
Stack Trace:
com.edb.MTKException: MTK-10045: The URL specified
for the
"target" database is invalid.
Check the connectivity credentials.



same error repeated even there mentioned correct
credentials
as here i checked multiple times


​Maybe you should try using an IP address instead of a
host name.

David J.​





I've never used this tool so like everyone else I am guessing here
but having quickly looked at the documentation have you tried
specifying the '-targetdbtype postgres' in addition to the
'-sourcedbtype sqlserver' that you have already specified?

Neil A




--
S

[GENERAL] Re: regarding schema only migration from sqlserver to postgres with runmtk.sh

[Neil Anderson]

On 2016-06-16 11:13 AM, Durgamahesh Manne wrote:

yes sir

as per above discussion..i already looked pdf postgres plus guide you
mentioned

as well as i already set password for postgres user associated with
postgres db

On Thu, Jun 16, 2016 at 8:30 PM, Durgamahesh Manne
> wrote:

hi
sir
as per above discussion same error repeated even mentioned ip
address of hostname

./runMTK.sh -sourcedbtype sqlserver -targetSchema public -schemaOnly
-allTables dbo


TARGET_DB_URL=jdbc:postgresql://192.168.168.201:5432/raghu

TARGET_DB_USER=postgres
TARGET_DB_PASSWORD=*



Connecting with source SQL Server database server...
Connected to Microsoft SQL Server, version '10.50.1600'
Connecting with target EnterpriseDB database server...
MTK-10045: The URL specified for the "target" database is invalid.
Check the connectivity credentials.
Stack Trace:
com.edb.MTKException: MTK-10045: The URL specified for the "target"
database is invalid.
Check the connectivity credentials.

On Thu, Jun 16, 2016 at 7:55 PM, David G. Johnston
> wrote:

On Thu, Jun 16, 2016 at 10:19 AM, Durgamahesh Manne
>wrote:


sir

PostgreSQL always no1 in world
as per above discussion

migration not working from sql to postgreSQL  with runmtk.sh


Connecting with source SQL Server database server...
Connected to Microsoft SQL Server, version '10.50.1600'
Connecting with target EnterpriseDB database server...
MTK-10045: The URL specified for the "target" database is
invalid.
Check the connectivity credentials.
Stack Trace:
com.edb.MTKException: MTK-10045: The URL specified for the
"target" database is invalid.
Check the connectivity credentials.



same error repeated even there mentioned correct credentials
as here i checked multiple times


​Maybe you should try using an IP address instead of a host name.

David J.​






I've never used this tool so like everyone else I am guessing here but 
having quickly looked at the documentation have you tried specifying the 
'-targetdbtype postgres' in addition to the '-sourcedbtype sqlserver' 
that you have already specified?


Neil A



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