Re: Sequences not moved to new tablespace

2015-02-24 10:39 GMT-05:00 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>:

> Guillaume Drolet wrote:
> >> If you want to move a whole database to a different tablespace (the
> only reason
> >> I can think of for doing what you are trying to so), use the command
> >> ALTER DATABASE ... SET TABLESPACE ...
>
> > Thanks Laurenz. I tried your suggestion:
> >
> > psql -U postgres -c "ALTER DATABASE mydb SET TABLESPACE pg_default;"
> >
> > I get this message:
> > ERROR: some relations of database "mortalite" are already in tablespace
> "pg_default"
> > HINT : You must move them back to the database's default tablespace
> before using this command.
> >
> > But if I do "SHOW default_tablespace;" in mydb, it showed "pg_default"
> as the default tablespace.
> >
> > So I tried changing it back to the tablespace I want to get rid of to
> subsequently moved everything
> > back there so that ultimately, it lets me move everything to pg_default:
> > ALTER DATABASE mydb SET default_tablespace = diamonds;
> >
> > And then:
> > psql -U postgres -c "ALTER DATABASE mydb SET TABLESPACE diamonds;"
> >
> > ALTER DATABASE is issued but nothing gets physically moved to diamonds.
> Why?
>
> I guess the problem is that you already moved a lot of tables around.
>
> Could you connect to the database and try the following:
>
> SELECT d.datname, d.oid, sp.spcname, sp.oid
> FROM pg_tablespace sp JOIN
> pg_database d ON sp.oid = d.dattablespace
> WHERE datname = current_database();
>

datname | oid | spcname | oid
-----------+--------+----------+--------
mydb| 942258 | diamonds | 940585
(1 row)

> and
>
> SELECT t.relname, t.reltablespace, sp.spcname
> FROM pg_class t LEFT JOIN
> pg_tablespace sp ON sp.oid = t.reltablespace;
>

relname | reltablespace
| spcname
----------------------------------------------------------+---------------+------------
geography_columns | 0 |
geometry_dump | 0 |
pg_statistic | 0 |
indexbdtq_wgs84_gid_seq | 0 |
mod09a1_sur_refl_b05_amonth_idx | 1663 |
pg_default
mod44b_cloud_rid_seq | 0 |
pg_toast_2619 | 0 |
pg_type | 0 |
pg_authid_rolname_index | 1664 |
pg_global
pg_authid_oid_index | 1664 |
pg_global
valid_detail | 0 |
pg_roles | 0 |
pg_shadow | 0 |
pg_group | 0 |
pg_inherits_parent_index | 0 |
pg_toast_1255 | 0 |
pg_database_datname_index | 1664 |
pg_global
pg_database_oid_index | 1664 |
pg_global
pg_am_name_index | 0 |
pg_am_oid_index | 0 |
pg_amop_fam_strat_index | 0 |
pg_amop_opr_fam_index | 0 |
pg_amop_oid_index | 0 |
pg_amproc_fam_proc_index | 0 |
pg_amproc_oid_index | 0 |
pg_aggregate_fnoid_index | 0 |
pg_toast_2618 | 0 |
pg_toast_2618_index | 0 |
pg_toast_2609 | 0 |
pg_toast_2609_index | 0 |
pg_cast_oid_index | 0 |
pg_cast_source_target_index | 0 |
pg_toast_2964 | 1664 |
pg_global
pg_toast_2964_index | 1664 |
pg_global
pg_auth_members_role_member_index | 1664 |
pg_global
pg_auth_members_member_role_index | 1664 |
pg_global
pg_toast_3596 | 0 |
pg_toast_3596_index | 0 |
pg_collation_oid_index | 0 |
pg_collation_name_enc_nsp_index | 0 |
pg_toast_2604 | 0 |
pg_toast_2620 | 0 |
pg_toast_2620_index | 0 |
pg_toast_2396 | 1664 |
pg_global
pg_toast_2396_index | 1664 |
pg_global
pg_user | 0 |
pg_toast_3998534_index | 1663 |
pg_default
pg_rules | 0 |
pg_views | 0 |
pg_tables | 0 |
pg_matviews | 0 |
pg_indexes | 0 |
pg_locks | 0 |
pg_opfamily_am_name_nsp_index | 0 |
pg_opfamily_oid_index | 0 |
pg_user_mapping_oid_index | 0 |
pg_user_mapping_user_server_index | 0 |
pg_language_name_index | 0 |
pg_language_oid_index | 0 |
pg_largeobject_metadata_oid_index | 0 |
pg_rewrite_oid_index | 0 |
pg_rewrite_rel_rulename_index | 0 |
pg_event_trigger_evtname_index | 0 |
pg_event_trigger_oid_index | 0 |
pg_description_o_c_o_index | 0 |
pg_enum_oid_index | 0 |
pg_enum_typid_label_index | 0 |
pg_namespace_nspname_index | 0 |
pg_namespace_oid_index | 0 |
pg_conversion_default_index | 0 |
pg_conversion_name_nsp_index | 0 |
pg_depend_depender_index | 0 |
pg_depend_reference_index | 0 |
pg_tablespace_oid_index | 1664 |
pg_global
pg_tablespace_spcname_index | 1664 |
pg_global
pg_pltemplate_name_index | 1664 |
pg_global
pg_shdepend_depender_index | 1664 |
pg_global
pg_shdepend_reference_index | 1664 |
pg_global
pg_ts_config_cfgname_index | 0 |
pg_ts_config_oid_index | 0 |
pg_ts_config_map_index | 0 |
pg_ts_dict_dictname_index | 0 |
pg_ts_dict_oid_index | 0 |
pg_opclass_am_name_nsp_index | 0 |
pg_opclass_oid_index | 0 |
pg_trigger_tgconstraint_index | 0 |
pg_trigger_tgrelid_tgname_index | 0 |
pg_shdescription_o_c_index | 1664 |
pg_global
pg_largeobject_loid_pn_index | 0 |
pg_settings | 0 |
pg_cursors | 0 |
pg_available_extensions | 0 |
pg_available_extension_versions | 0 |
pg_prepared_xacts | 0 |
pg_prepared_statements | 0 |
pg_seclabels | 0 |
pg_timezone_abbrevs | 0 |
pg_timezone_names | 0 |
pg_stat_all_tables | 0 |
pg_stat_xact_all_tables | 0 |
pg_stat_sys_tables | 0 |
pg_stat_xact_sys_tables | 0 |
pg_stat_user_tables | 0 |
pg_stat_xact_user_tables | 0 |
pg_statio_all_tables | 0 |
pg_statio_sys_tables | 0 |
mcd12q1_land_cover_type_1_pkey | 1663 |
pg_default
pg_type_oid_index | 0 |
pg_type_typname_nsp_index | 0 |
pg_authid | 1664 |
pg_global
pg_statio_user_tables | 0 |
pg_stat_all_indexes | 0 |
sequences | 0 |
pg_statio_sys_indexes | 0 |
pg_statio_user_indexes | 0 |
pg_class | 0 |
pg_statio_all_sequences | 0 |
pg_statio_sys_sequences | 0 |
pg_extension_oid_index | 0 |
pg_foreign_server_oid_index | 0 |
pg_foreign_server_name_index | 0 |
pg_foreign_table_relid_index | 0 |
pg_default_acl_role_nsp_obj_index | 0 |
pg_default_acl_oid_index | 0 |
pg_seclabel_object_index | 0 |
pg_shseclabel_object_index | 1664 |
pg_global
pg_foreign_data_wrapper_oid_index | 0 |
pg_foreign_data_wrapper_name_index | 0 |
pg_range_rngtypid_index | 0 |
pg_statio_user_sequences | 0 |
pg_stat_activity | 0 |
pg_stat_replication | 0 |
pg_stat_database | 0 |
pg_stat_database_conflicts | 0 |
pg_stat_user_functions | 0 |
pg_stat_xact_user_functions | 0 |
pg_stat_bgwriter | 0 |
pg_user_mappings | 0 |
area_gid_seq | 0 |
mod09a1_sur_refl_b05_aday_idx | 1663 |
pg_default
pg_stats | 0 |
pg_stat_sys_indexes | 0 |
pg_stat_user_indexes | 0 |
pg_statio_all_indexes | 0 |
information_schema_catalog_name | 0 |
applicable_roles | 0 |
administrable_role_authorizations | 0 |
attributes | 0 |
character_sets | 0 |
check_constraint_routine_usage | 0 |
pg_attribute | 0 |
pg_constraint | 0 |
pg_inherits | 0 |
pg_index | 0 |
pg_operator | 0 |
pg_opfamily | 0 |
pg_user_mapping | 0 |
pg_proc | 0 |
pg_database | 1664 |
pg_global
pg_am | 0 |
pg_amop | 0 |
pg_amproc | 0 |
pg_language | 0 |
pg_largeobject_metadata | 0 |
pg_aggregate | 0 |
pg_rewrite | 0 |
check_constraints | 0 |
collations | 0 |
mod09a1_sur_refl_b06_ayear_idx | 1663 |
pg_default
collation_character_set_applicability | 0 |
column_domain_usage | 0 |
column_privileges | 0 |
column_udt_usage | 0 |
columns | 0 |
constraint_column_usage | 0 |
constraint_table_usage | 0 |
domain_constraints | 0 |
domain_udt_usage | 0 |
domains | 0 |
enabled_roles | 0 |
key_column_usage | 0 |
pg_cast | 0 |
pg_enum | 0 |
pg_namespace | 0 |
pg_conversion | 0 |
pg_depend | 0 |
pg_db_role_setting | 1664 |
pg_global
pg_tablespace | 1664 |
pg_global
pg_pltemplate | 1664 |
pg_global
pg_auth_members | 1664 |
pg_global
pg_shdepend | 1664 |
pg_global
pg_ts_config | 0 |
pg_ts_config_map | 0 |
pg_ts_dict | 0 |
pg_ts_parser | 0 |
pg_ts_template | 0 |
pg_extension | 0 |
pg_foreign_server | 0 |
pg_foreign_table | 0 |
pg_default_acl | 0 |
pg_seclabel | 0 |
pg_shseclabel | 1664 |
pg_global
pg_collation | 0 |
parameters | 0 |
referential_constraints | 0 |
role_column_grants | 0 |
routine_privileges | 0 |
role_routine_grants | 0 |
routines | 0 |
schemata | 0 |
geometry_columns | 0 |
rastbandarg | 0 |
geomval | 0 |
addbandarg | 0 |
table_constraints | 0 |
table_privileges | 0 |
role_table_grants | 0 |
tables | 0 |
triggered_update_columns | 0 |
triggers | 0 |
udt_privileges | 0 |
role_udt_grants | 0 |
usage_privileges | 0 |
role_usage_grants | 0 |
mod09a1_sur_refl_b06_amonth_idx | 1663 |
pg_default
pg_toast_2619_index | 0 |
user_defined_types | 0 |
view_column_usage | 0 |
view_routine_usage | 0 |
view_table_usage | 0 |
views | 0 |
data_type_privileges | 0 |
element_types | 0 |
_pg_foreign_table_columns | 0 |
column_options | 0 |
_pg_foreign_data_wrappers | 0 |
foreign_data_wrapper_options | 0 |
foreign_data_wrappers | 0 |
_pg_foreign_servers | 0 |
foreign_server_options | 0 |
pg_toast_11618 | 1663 |
pg_default
foreign_servers | 0 |
_pg_foreign_tables | 0 |
foreign_table_options | 0 |
foreign_tables | 0 |
_pg_user_mappings | 0 |
user_mapping_options | 0 |
user_mappings | 0 |
reclassarg | 0 |
agg_samealignment | 0 |
unionarg | 0 |
raster_columns | 0 |
raster_overviews | 0 |
mod09a1_sur_refl_b06_aday_idx | 1663 |
pg_default
pg_statistic_relid_att_inh_index | 0 |
station_idx | 1663 |
pg_default
mcd12q2_nbar_evi_onset_greenness_maximum_pkey | 1663 |
pg_default
mcd12q2_nbar_evi_onset_greenness_maximum_gist_idx | 1663 |
pg_default
mcd12q2_nbar_evi_onset_greenness_maximum_ayear_idx | 1663 |
pg_default
mcd12q1_land_cover_type_1_gist_idx | 1663 |
pg_default
mcd12q1_land_cover_type_1_ayear_idx | 1663 |
pg_default
mcd12q1_land_cover_type_1_amonth_idx | 1663 |
pg_default
mcd12q1_land_cover_type_1_aday_idx | 1663 |
pg_default
extent_tight_gid_seq | 0 |
validatetopology_returntype | 0 |
topogeometry | 0 |
...
...
(1613 rows)