Did you know it’s possible to export certain posts using WP-CLI? I didn’t, but a big thanks to Daniel Bachhuber for pointing me in the right direction. That led to my first contribution back to the WP-CLI project which makes me very happy 😄. Here are the docs I added.
# Export certain posts without create table statements
$ wp db export --no-create-info=true --tables=wp_posts --where="ID in (100,101,102)"
Success: Exported to 'wordpress_dbase-db72bb5.sql'.
# Export relating meta for certain posts without create table statements
$ wp db export --no-create-info=true --tables=wp_postmeta --where="post_id in (100,101,102)"
Success: Exported to 'wordpress_dbase-db72bb5.sql'.Selective exports/imports work in very specific situations.
Ok so for the most part you wouldn’t actually want to do this as importing specific IDs from one WordPress site to another will most likely override data. That said there are specific situations where it makes sense. For example, let’s say you cloned a live site over to your local development environment. Now a few months go by and you really just want to pull over one particular post without completely re-creating the local environment. That’s easily scriptable.
Using bash script to sync specific post IDs between remote and local WordPress sites.
The following master-db-query.sh file can be run on over SSH on a remote WordPress site to output SQL for specific post IDs.
#!/bin/bash
#
# File 'master-db-query.sh'
# Export select ids from remote server
#
ids_to_sync=$@
function join_by { local IFS="$1"; shift; echo "$*"; }
ids=$(join_by , ${ids_to_sync})
wp db export - --no-create-info=true --tables=wp_posts --where="ID in ($site_ids)"
wp db export - --no-create-info=true --tables=wp_postmeta --where="post_id in ($site_ids)"The following is an example of using master-db-query.sh to capture SQL output from the remote server and import on a local WordPress site.
# Sync remote post IDs with local WordPress site
ids_to_sync=( 100 101 102 )
wp db query "delete from wp_posts where ID in (${ids_to_sync[@]});" # Purge local post IDs
wp db query "delete from wp_postmeta where post_id in (${ids_to_sync[@]});" # Purge local postmeta for post IDs
ssh remote-server "bash -s" < master-db-query.sh ${ids_to_sync[@]} > sync.sql # Export specific post IDs from remote to local sync.sql file
wp db import sync.sql # import posts locally