how to restore a table in clan

Wed, Jan 24, 2007

So the problem was rebuild the special_groups_a column of all users in CLAN. First thing was to dump the required table from a database known to have correct values for this column:

Dump table “users” from database “clan-beta” to file “beta-users.sql”: pg_dump -t users -f beta-users.sql clan-beta

Before we can use the SQL dump, we should clean it up as we’re not going to use pg_restore. So delete all lines starting “–”, “SET”, blank lines and lines starting with “ALTER” and “ADD”. You should now be left with a raw SQL file.

Next we have to rename the table in the SQL:

change CREATE TABLE users ( to  CREATE TABLE betausers (

Now import the new SQL statements:

pqsl clan-live < beta-users.sql

When it’s done, go into the clan database:

psql clan

and use the following SQL to update the “users” table, “special_groups_a” column from the newly imported “betausers” table:

update users set special_groups_a = (select special_groups_a from betausers where betausers.user_id = users.user_id) where users.user_id = betausers.user_id

and that will rebuild the special_groups_a column for all users.

comments powered by Disqus