search

eulerto / pgquarrel

pgquarrel compares PostgreSQL database schemas (DDL)
BSD 3-Clause "New" or "Revised" License
388 stars 42 forks source link

Compatibility with nix and google cloud sql #33

Closed wereHamster closed 6 years ago

wereHamster commented 7 years ago

⚠️ NOT INTENDED FOR MERGING ⚠️

I tried to use this tool to migrate between two databases running on google cloud sql. The postgres version of google cloud sql is still in beta, but working nicely for me so far.

First of all, I used nix to build this binary. The default.nix file is included and rather simple. I had to change the CMakeLists.txt file and include the OpenSSL dependency. Without adding OPENSSL_LIBRARIES to LIBS, the linker wouldn't find the symbols from libssl.

Second, google cloud restricts access to the pg_user_mapping table, so I commented out that part.

ERROR query failed: ERROR:  permission denied for relation pg_user_mapping

Could be nice to have this as an option in the [global] section of the config file.

Note that I only tested it with one simple table. There might be other things which are inaccessible in google cloud sdk.

eulerto commented 6 years ago

@wereHamster I can't reproduce the SSL issue. Please provide some information:

It is in my roadmap providing configuration options to select objects.

wereHamster commented 6 years ago

OS is Mac 10.12.6, cmake 3.8.2, postgres 9.6.3:

[nix-shell:~/dev/pgquarrel]$ cmake .
-- The C compiler identification is AppleClang 8.0.0.8000038
-- Check for working C compiler: /usr/bin/cc
-- Check for working C compiler: /usr/bin/cc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Detecting C compile features
-- Detecting C compile features - done
-- Found PostgreSQL: /nix/store/9phr02pxdn6fwk545kvwpymxqfpv63ar-postgresql-9.6.3/lib/libpq.a (found version "9.6.3") 
-- Configuring done
CMake Warning (dev):
  Policy CMP0042 is not set: MACOSX_RPATH is enabled by default.  Run "cmake
  --help-policy CMP0042" for policy details.  Use the cmake_policy command to
  set the policy and suppress this warning.

  MACOSX_RPATH is not specified for the following targets:

   mini

This warning is for project developers.  Use -Wno-dev to suppress it.

-- Generating done
-- Build files have been written to: /Users/tomc/dev/pgquarrel

[nix-shell:~/dev/pgquarrel]$ make
Scanning dependencies of target mini
[  3%] Building C object mini/CMakeFiles/mini.dir/mini-file.c.o
[  6%] Building C object mini/CMakeFiles/mini.dir/mini-parser.c.o
[  9%] Building C object mini/CMakeFiles/mini.dir/mini-readline.c.o
[ 12%] Building C object mini/CMakeFiles/mini.dir/mini-strip.c.o
[ 15%] Linking C shared library libmini.dylib
[ 15%] Built target mini
Scanning dependencies of target pgquarrel
[ 18%] Building C object CMakeFiles/pgquarrel.dir/src/aggregate.c.o
[ 21%] Building C object CMakeFiles/pgquarrel.dir/src/cast.c.o
[ 25%] Building C object CMakeFiles/pgquarrel.dir/src/collation.c.o
[ 28%] Building C object CMakeFiles/pgquarrel.dir/src/common.c.o
[ 31%] Building C object CMakeFiles/pgquarrel.dir/src/conversion.c.o
[ 34%] Building C object CMakeFiles/pgquarrel.dir/src/domain.c.o
[ 37%] Building C object CMakeFiles/pgquarrel.dir/src/eventtrigger.c.o
[ 40%] Building C object CMakeFiles/pgquarrel.dir/src/extension.c.o
[ 43%] Building C object CMakeFiles/pgquarrel.dir/src/fdw.c.o
[ 46%] Building C object CMakeFiles/pgquarrel.dir/src/function.c.o
[ 50%] Building C object CMakeFiles/pgquarrel.dir/src/index.c.o
[ 53%] Building C object CMakeFiles/pgquarrel.dir/src/language.c.o
[ 56%] Building C object CMakeFiles/pgquarrel.dir/src/matview.c.o
[ 59%] Building C object CMakeFiles/pgquarrel.dir/src/operator.c.o
[ 62%] Building C object CMakeFiles/pgquarrel.dir/src/privileges.c.o
[ 65%] Building C object CMakeFiles/pgquarrel.dir/src/quarrel.c.o
/Users/tomc/dev/pgquarrel/src/quarrel.c:3040:1: warning: unused function 'quarrelUserMappings' [-Wunused-function]
quarrelUserMappings()
^
1 warning generated.
[ 68%] Building C object CMakeFiles/pgquarrel.dir/src/rule.c.o
[ 71%] Building C object CMakeFiles/pgquarrel.dir/src/schema.c.o
[ 75%] Building C object CMakeFiles/pgquarrel.dir/src/sequence.c.o
[ 78%] Building C object CMakeFiles/pgquarrel.dir/src/server.c.o
[ 81%] Building C object CMakeFiles/pgquarrel.dir/src/table.c.o
[ 84%] Building C object CMakeFiles/pgquarrel.dir/src/textsearch.c.o
[ 87%] Building C object CMakeFiles/pgquarrel.dir/src/trigger.c.o
[ 90%] Building C object CMakeFiles/pgquarrel.dir/src/type.c.o
[ 93%] Building C object CMakeFiles/pgquarrel.dir/src/usermapping.c.o
[ 96%] Building C object CMakeFiles/pgquarrel.dir/src/view.c.o
[100%] Linking C executable pgquarrel
Undefined symbols for architecture x86_64:
  "_ASN1_STRING_data", referenced from:
      _verify_peer_name_matches_certificate_name in libpq.a(fe-secure-openssl.o)
  "_ASN1_STRING_length", referenced from:
      _verify_peer_name_matches_certificate_name in libpq.a(fe-secure-openssl.o)
  "_BIO_clear_flags", referenced from:
      _my_sock_read in libpq.a(fe-secure-openssl.o)
      _my_sock_write in libpq.a(fe-secure-openssl.o)
  "_BIO_int_ctrl", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_BIO_new", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_BIO_s_socket", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_BIO_set_flags", referenced from:
      _my_sock_read in libpq.a(fe-secure-openssl.o)
      _my_sock_write in libpq.a(fe-secure-openssl.o)
  "_CRYPTO_get_id_callback", referenced from:
      _pgtls_close in libpq.a(fe-secure-openssl.o)
      _pgtls_init in libpq.a(fe-secure-openssl.o)
  "_CRYPTO_get_locking_callback", referenced from:
      _pgtls_close in libpq.a(fe-secure-openssl.o)
      _pgtls_init in libpq.a(fe-secure-openssl.o)
  "_CRYPTO_num_locks", referenced from:
      _pgtls_init in libpq.a(fe-secure-openssl.o)
  "_CRYPTO_set_id_callback", referenced from:
      _pgtls_close in libpq.a(fe-secure-openssl.o)
      _pgtls_init in libpq.a(fe-secure-openssl.o)
  "_CRYPTO_set_locking_callback", referenced from:
      _pgtls_close in libpq.a(fe-secure-openssl.o)
      _pgtls_init in libpq.a(fe-secure-openssl.o)
  "_ENGINE_by_id", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_ENGINE_finish", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
      _pgtls_close in libpq.a(fe-secure-openssl.o)
  "_ENGINE_free", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
      _pgtls_close in libpq.a(fe-secure-openssl.o)
  "_ENGINE_init", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_ENGINE_load_private_key", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_ERR_clear_error", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
      _pgtls_read in libpq.a(fe-secure-openssl.o)
      _pgtls_write in libpq.a(fe-secure-openssl.o)
  "_ERR_get_error", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
      _pgtls_read in libpq.a(fe-secure-openssl.o)
      _pgtls_write in libpq.a(fe-secure-openssl.o)
  "_ERR_put_error", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_ERR_reason_error_string", referenced from:
      _SSLerrmessage in libpq.a(fe-secure-openssl.o)
  "_OPENSSL_config", referenced from:
      _pgtls_init in libpq.a(fe-secure-openssl.o)
  "_SSL_CIPHER_get_bits", referenced from:
      _PQsslAttribute in libpq.a(fe-secure-openssl.o)
  "_SSL_CIPHER_get_name", referenced from:
      _PQsslAttribute in libpq.a(fe-secure-openssl.o)
  "_SSL_CTX_ctrl", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_CTX_free", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_CTX_get_cert_store", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_CTX_load_verify_locations", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_CTX_new", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_CTX_use_certificate_chain_file", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_check_private_key", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_connect", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_ctrl", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_free", referenced from:
      _pgtls_close in libpq.a(fe-secure-openssl.o)
  "_SSL_get_current_cipher", referenced from:
      _PQsslAttribute in libpq.a(fe-secure-openssl.o)
  "_SSL_get_current_compression", referenced from:
      _PQsslAttribute in libpq.a(fe-secure-openssl.o)
  "_SSL_get_error", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
      _pgtls_read in libpq.a(fe-secure-openssl.o)
      _pgtls_write in libpq.a(fe-secure-openssl.o)
  "_SSL_get_peer_certificate", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_get_version", referenced from:
      _PQsslAttribute in libpq.a(fe-secure-openssl.o)
  "_SSL_library_init", referenced from:
      _pgtls_init in libpq.a(fe-secure-openssl.o)
  "_SSL_load_error_strings", referenced from:
      _pgtls_init in libpq.a(fe-secure-openssl.o)
  "_SSL_new", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_pending", referenced from:
      _pgtls_read_pending in libpq.a(fe-secure-openssl.o)
  "_SSL_read", referenced from:
      _pgtls_read in libpq.a(fe-secure-openssl.o)
  "_SSL_set_bio", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_set_ex_data", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_set_verify", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_shutdown", referenced from:
      _pgtls_close in libpq.a(fe-secure-openssl.o)
  "_SSL_use_PrivateKey", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_use_PrivateKey_file", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_SSL_write", referenced from:
      _pgtls_write in libpq.a(fe-secure-openssl.o)
  "_SSLv23_method", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_X509_NAME_ENTRY_get_data", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_X509_NAME_get_entry", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_X509_NAME_get_index_by_NID", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_X509_STORE_load_locations", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_X509_STORE_set_flags", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_X509_free", referenced from:
      _pgtls_close in libpq.a(fe-secure-openssl.o)
  "_X509_get_ext_d2i", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_X509_get_subject_name", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_sk_free", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_sk_num", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
  "_sk_value", referenced from:
      _pgtls_open_client in libpq.a(fe-secure-openssl.o)
ld: symbol(s) not found for architecture x86_64
clang: error: linker command failed with exit code 1 (use -v to see invocation)
make[2]: *** [CMakeFiles/pgquarrel.dir/build.make:747: pgquarrel] Error 1
make[1]: *** [CMakeFiles/Makefile2:68: CMakeFiles/pgquarrel.dir/all] Error 2
make: *** [Makefile:130: all] Error 2
eulerto commented 6 years ago

@wereHamster I don't have Mac in hand. I just need to check the compiler command line during linkage.

make clean make VERBOSE=1

wereHamster commented 6 years ago 
[ 18%] Linking C executable pgquarrel
/nix/store/7mijyjx116ab5sprsjj3yv9vl2mxfjjm-cmake-3.8.2/bin/cmake -E cmake_link_script CMakeFiles/pgquarrel.dir/link.txt --verbose=1
/usr/bin/cc -I/usr/local/include -O0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -arch x86_64 -isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.12.sdk -mmacosx-version-min=10.10 -Wl,-search_paths_first -Wl,-headerpad_max_install_names -L/usr/local/lib CMakeFiles/pgquarrel.dir/src/aggregate.c.o CMakeFiles/pgquarrel.dir/src/cast.c.o CMakeFiles/pgquarrel.dir/src/collation.c.o CMakeFiles/pgquarrel.dir/src/common.c.o CMakeFiles/pgquarrel.dir/src/conversion.c.o CMakeFiles/pgquarrel.dir/src/domain.c.o CMakeFiles/pgquarrel.dir/src/eventtrigger.c.o CMakeFiles/pgquarrel.dir/src/extension.c.o CMakeFiles/pgquarrel.dir/src/fdw.c.o CMakeFiles/pgquarrel.dir/src/function.c.o CMakeFiles/pgquarrel.dir/src/index.c.o CMakeFiles/pgquarrel.dir/src/language.c.o CMakeFiles/pgquarrel.dir/src/matview.c.o CMakeFiles/pgquarrel.dir/src/operator.c.o CMakeFiles/pgquarrel.dir/src/privileges.c.o CMakeFiles/pgquarrel.dir/src/quarrel.c.o CMakeFiles/pgquarrel.dir/src/rule.c.o CMakeFiles/pgquarrel.dir/src/schema.c.o CMakeFiles/pgquarrel.dir/src/sequence.c.o CMakeFiles/pgquarrel.dir/src/server.c.o CMakeFiles/pgquarrel.dir/src/table.c.o CMakeFiles/pgquarrel.dir/src/textsearch.c.o CMakeFiles/pgquarrel.dir/src/trigger.c.o CMakeFiles/pgquarrel.dir/src/type.c.o CMakeFiles/pgquarrel.dir/src/usermapping.c.o CMakeFiles/pgquarrel.dir/src/view.c.o  -o pgquarrel /nix/store/9phr02pxdn6fwk545kvwpymxqfpv63ar-postgresql-9.6.3/lib/libpq.a mini/libmini.dylib 
Undefined symbols for architecture x86_64:
  "_ASN1_STRING_data", referenced from:
      _verify_peer_name_matches_certificate_name in libpq.a(fe-secure-openssl.o)
eulerto commented 6 years ago

@wereHamster I can't test because I don't own a Mac. Could you test if the last commit solve the OpenSSL issue?

eulerto commented 6 years ago

@wereHamster commit ea26815262313dbe3ba6eb216f052ca498ed8a50 added options to select the objects you want to compare.