Pgdiff can't compare not public database schemas

[Rickkk]

Hi! In our database 10 different database schemas with many objects. I would like to transfer changes in this schemas to database recipient, but pgdiff create scipt with only objects in public schema. How can I solve this problem?

[joncrlsn]

Rick, I'm really sorry I haven't gotten to this yet. I'll do my best to get it done this weekend. Bug me on Monday if you don't see any changes. Knowing you'll do that should help me get it done this weekend. :-)

[Rickkk]

Thank you. It's very pleasant that you react so quickly to this problem.

[joncrlsn]

Rick, Would you be OK if you were able to compare one schema at a time? I'm thinking about having the user specify a schema for each database, so someone could compare two schemas with different names. But I suspect that most of the time the schema names will be the same.

I may be able to have you specify a star for the schema so it will compare all non-postgres schemas in one shot.

[Rickkk]

That's a good question, Jon. My case is the one when in both bases the compared schemes with the same names. However, from the point of view of the architecture of the application, it is more correct if you can specify schema names for both databases. And maybe in some cases, they will sometimes be different.

[Rickkk]

Hello, Jon! When will the new release ?

[joncrlsn]

It depends on how much I get done this weekend. Sorry I can't tell you more than that.

[joncrlsn]

Rick, Doh! And good news. I just realized that I created a branch 0.9.3 that compares all schemas in the database except the system schemas. But I never merged that branch into master because I was wanting more feedback. Could you test the binaries in this branch and let me know if that will work for you?

https://github.com/joncrlsn/pgdiff/tree/release/0.9.3

This won't allow you to compare two schemas unless they have the same name, so this doesn't do everything we discussed, but I think it will do what you need right now. Please let me know how it works for you. Thanks!

[Rickkk]

Thanks Jon for release! But I can not test branch 0.9.3 . I use Windows, but binary in folder bin-win is old. It's not contained last chandes. I tried to compile the executable for windows with the help of the compiler Go but there were errors. Could you recompile all binaries with last changes in sources?

[joncrlsn]

Hi Rick, Try this. I can't promise it will work completely because I don't have a full database to test things with anymore. I'm remedying that with scripts that create small databases to test individual portions of a schema in a more controlled way. Anyway, use -S '*' and -s '*' to compare all schemas except the postgres ones.

https://github.com/joncrlsn/pgdiff/files/1409302/pgdiff-0.9.3-beta.2-windows.zip

Best wishes. Let me know if you get an error you don't understand.

[Rickkk]

Thank you Jon for windows binary file. I tested it and found problems.

There are my remarks about problems in pgdiff:

1. Every body of function does not ends semicolon. So I can not run output script without errors - it is necessary to correct the end of each function manually.

Example
CREATE OR REPLACE FUNCTION config.exceptionslogger(z_sql text, get_result boolean DEFAULT false, extra_raise_exception boolean DEFAULT false)
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
declare

BEGIN

.....
return 1000;
END;
$function$
--there is no semicolon

In our database some columns has name 'limit' . It name matches the SQL language keyword. For this reason
output script has errors. Can you wrap databse identifiers (columns,tables,schema names,functions, etc) in double quotes?
In this case the sql code:

alter table "crosses"."links_logs" add column "limit" varchar(10);

will not have problems.

I launched your application for get difference between functions not only public schema. But application
can not compare functions not from the public schema, only from public schema. Please check compare functions not from the public schema.

Thank you for attention

[joncrlsn]

OK, I hadn't heard of double-quotes working. I've only seen single-quotes in SQL, so I'll try both.

I'm making my way through the db types on the 0.9.3 branch and making them work either for all non-public or for comparing two different schemas. It's slower than I had hoped it would be given that it needs some different code when comparing between schemas.

I'll check the functions code. It's possible I missed that.

[clebermasters]

Hello Jon,
I am trying to use pgdiff with parameter -S/-s but it's seen that an unknown command.

./pgdiff -U mds -W mds -H yyy.xxx.com -P 5432 -D val1 -O 'sslmode=disable' -S "test"
-u mds -w mds -h yyyy.xxx.com -p 5432 -d dev2 -o 'sslmode=disable' -s "test"
COLUMN

There is something wrong ? I am using 0.9.3 version

Thanks !

[joncrlsn]

That should work. Are you compiling it from the source? If not, try the beta release code here: https://github.com/joncrlsn/pgdiff/releases

The links in the main README are pointing to old binaries... I'm working to fix that.

[joncrlsn]

@Rickkk The code to specify schemas to compare is done so I created a 1.0 beta 1 release for that and updated the download links in the README on the master branch. The work took a lot longer than I thought it would.

I also suffixed the function definitions with semi-colons so that is done too. I will work next on quoting the table and column names while I wait for more feedback. Let me know how it goes for you. Thanks!