Specify INSERT columns with query
Altering a columns position is going to require a full table rewrite. My suggestion is not to alter column positions. However, there is an abundance of people doing this and there are numerous ways to do it. In my experience,


one way is far better and easier, if you have the privileges, and
most DBAs have those privileges.


My suggestion is to use pg_dump.


Dump with --column-inserts


  Dump data as INSERT commands with explicit column names (INSERT
  INTO table (column, ...) VALUES ...). This will make restoration
  very slow; it is mainly useful for making dumps that can be loaded
  into non-PostgreSQL databases. However, since this option generates
  a separate command for each row, an error in reloading a row causes
  only that row to be lost rather than the entire table contents.

Remove from dump what you don't need.
Modify table definition or ordering.
In a transaction,


Drop the old tables 
Execute the script creating the new tables.
Commit