2015-02-24 10:39 GMT-05:00 Albe Laurenz <laurenz.a...@wien.gv.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)