There are a number of ways to export and import data from a database. You can use a Mac client like Sequal Pro for MySQL, for example. You can do the same for PostgreSQL, though I have not settled on a Mac client for PostgreSQL.
Mac clients aside, you need can import and export your database SQL from the command line or from a bash script on your server as well. I use the following commands to handle importing and exporting SQL on my servers and development machines.
If you want to export PostgreSQL:
pg_dump -U [username] [database] -f [filepath]
If you want to export MySQL:
mysqldump -u [username] -p[root_password] [database] > [filepath]
Note that there is no space after -p in the MySQL command.
If you want to import PostgreSQL:
psql [database] < [filepath]
Peer Authentication Fix
Note that in PostgreSQL you might get an error: "psql: FATAL: Peer authentication failed for user". I get this a lot becasue I use Laravel Homestead. You may be able to fix this error by editing your
pg_hba.conf file. To find this file on Ubuntu update your file index using
sudo updatedb and then search for the file using
You should see a list of files. For example:
In the main
/ect file location at the bottom of the
pg_hba.conf file you will see:
# DO NOT DISABLE! # If you change this first entry you will need to make sure that the # database superuser can access the database using some other method. # Noninteractive access to all databases is required during automatic # maintenance (custom daily cronjobs, replication, and similar tasks). # # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 md5 #host replication postgres ::1/128 md5 host all all 10.0.2.2/32 md5
peer in this section to
md5 and save your changes. Then restart
postgresql using the command
sudo service postgresql restart. You can now conect and import your data using
Also, you might need to specify the login user. For example, if my user is homestead I need to run:
psql [database] < [filepath] -U homestead
Create a Table
In some cases you may not have an import table. You can create on with
psql as well.
psql postgres -U homestead
Then run the SQL:
CREATE DATABASE [database]; GRANT ALL PRIVILEGES ON DATABASE [database] TO [username];
If you want to import MySQL:
mysql -u [username] -p[root_password] [database] < [filepath]
You can read about the backup process for PostgreSQL on their website for more details.