Re: Sequences not moved to new tablespace - Mailing list pgsql-general
| From | Guillaume Drolet |
|---|---|
| Subject | Re: Sequences not moved to new tablespace |
| Date | |
| Msg-id | CAOkiyv4MKhGpZBT+K5O0eYW4JPhbctueEE46_M=6XgLRjF3NNg@mail.gmail.com Whole thread Raw |
| In response to | Re: Sequences not moved to new tablespace (Albe Laurenz <laurenz.albe@wien.gv.at>) |
| Responses |
Re: Sequences not moved to new tablespace
|
| List | pgsql-general |
2015-02-24 10:39 GMT-05:00 Albe Laurenz <laurenz.albe@wien.gv.at>:
datname | oid | spcname | oid
-----------+--------+----------+--------
mydb| 942258 | diamonds | 940585
(1 row)
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 |
...
...
----------------------------------------------------------+---------------+------------
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)
pgsql-general by date: