在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
开源软件名称:pgsync开源软件地址:https://gitee.com/mirrors/pgsync开源软件介绍:pgsyncSync data from one Postgres database to another (like
Battle-tested at Instacart Installationpgsync is a command line tool. To install, run: gem install pgsync This will give you the SetupIn your project directory, run: pgsync --init This creates How to UseFirst, make sure your schema is set up in both databases. We recommend using a schema migration tool for this, but pgsync also provides a few convenience methods. Once that’s done, you’re ready to sync data. Sync tables pgsync Sync specific tables pgsync table1,table2 Works with wildcards as well pgsync "table*" Sync specific rows (existing rows are overwritten) pgsync products "where store_id = 1" You can also preserve existing rows pgsync products "where store_id = 1" --preserve Or truncate them pgsync products "where store_id = 1" --truncate TablesExclude specific tables pgsync --exclude table1,table2 Add to exclude: - table1 - table2 Sync tables from all schemas or specific schemas (by default, only the search path is synced) pgsync --all-schemas# orpgsync --schemas public,other# orpgsync public.table1,other.table2 GroupsDefine groups in groups: group1: - table1 - table2 And run: pgsync group1 VariablesYou can also use groups to sync a specific record and associated records in other tables. To get product groups: product: products: "where id = {1}" reviews: "where product_id = {1}" coupons: "where product_id = {1} order by created_at desc limit 10" stores: "where id in (select store_id from products where id = {1})" And run: pgsync product:123 SchemaSync schema before the data (this wipes out existing data) pgsync --schema-first Specify tables pgsync table1,table2 --schema-first Or just the schema pgsync --schema-only pgsync does not try to sync Postgres extensions. Sensitive DataPrevent sensitive data like email addresses from leaving the remote server. Define rules in data_rules: email: unique_email last_name: random_letter birthday: random_date users.auth_token: value: secret visits_count: statement: "(RANDOM() * 10)::int" encrypted_*: null
Options for replacement are:
Rules starting with Foreign KeysForeign keys can make it difficult to sync data. Three options are:
To defer constraints, use: pgsync --defer-constraints To manually specify the order of tables, use pgsync table1,table2,table3 --jobs 1 To disable foreign key triggers and potentially break referential integrity, use: pgsync --disable-integrity This requires superuser privileges on the TriggersDisable user triggers with: pgsync --disable-user-triggers Append-Only TablesFor extremely large, append-only tables, sync in batches. pgsync large_table --in-batches The script will resume where it left off when run again, making it great for backfills. Connection SecurityAlways make sure your connection is secure when connecting to a database over a network you don’t fully trust. Your best option is to connect over SSH or a VPN. Another option is to use SafetyTo keep you from accidentally overwriting production, the destination is limited to To use another host, add Multiple DatabasesTo use with multiple databases, run: pgsync --init db2 This creates pgsync --db db2 IntegrationsDjangoIf you run exclude: - django_migrations HerokuIf you run from: $(heroku config:get DATABASE_URL)?sslmode=require LaravelIf you run exclude: - migrations RailsIf you run exclude: - ar_internal_metadata - schema_migrations DebuggingTo view the SQL that’s run, use: pgsync --debug Other CommandsHelp pgsync --help Version pgsync --version List tables pgsync --list ScriptsUse groups when possible to take advantage of parallelism. For Ruby scripts, you may need to do: Bundler.with_unbundled_env do system "pgsync ..."end HomebrewOn Mac, you can use: brew install ankane/brew/pgsync DependenciesIf installation fails, your system may be missing Ruby or libpq. On Mac, run: brew install postgresql On Ubuntu, run: sudo apt-get install ruby-dev libpq-dev build-essential UpgradingRun: gem install pgsync To use master, run: gem install specific_installgem specific_install https://github.com/ankane/pgsync.git Related ProjectsAlso check out:
ThanksInspired by heroku-pg-transfer. HistoryView the changelog ContributingEveryone is encouraged to help improve this project. Here are a few ways you can help:
To get started with development: git clone https://github.com/ankane/pgsync.gitcd pgsyncbundle installcreatedb pgsync_test1createdb pgsync_test2createdb pgsync_test3bundle exec rake test |
请发表评论